Re: Evaluating Complex Recordset Field Statements
- From: "Kevin Rollo" <krollo041@xxxxxxxxxxx>
- Date: Sun, 04 Sep 2005 09:40:06 GMT
Constructing the altered query for the recordset seems to be the best way to
go. It's easy enough to build the data driven from the reference "fields"
table and ensuring the use of unique calc field names that you know have no
chance of already existing in the table keeps it all workable. I did have a
quick go at building a parser to interpret the complex statement, but was
awed at how much coding was involved, and arrived at the assumption that the
query was going to be far more efficient anyway.
--
Regards,
Kevin
"David W. Fenton" <dXXXfenton@xxxxxxxxxxxxxxxx> wrote in message
news:Xns96C6C5528B2B0dfentonbwaynetinvali@xxxxxxxxxxxxxxxxx
"Kevin Rollo" <krollo041@xxxxxxxxxxx> wrote in
news:95iSe.21263$FA3.5106@xxxxxxxxxxxxxxxxxxxxxxxxxx:
> I'm playing with a generic routine to export data, the concept is
> to have a list of data driven templates defining what fields to
> output.
>
> Evaluating a simple variable field name in the function is easily
> achieved by something like
>
> dim rst as dao.recordset
> ....
> strField = "Amount"
> debug.print rst.fields(strField) ' or just rst(strField)
>
> What I'm interested in is seeing if there is a way to evaluate a
> complex statement such as
>
> strField = "Amount * Tax"
> or even embedding a formula like strField =
> "format([MyDate],"dd-mmm")" rst(strField) doesn't work because
> there is no field explicitly called
> [Amount * Tax]
>
> I've tried a few variants like
> strField = "=[Amount * Tax]"
> strField = "rst("Amount") * rst("Tax")" but it doesn't know
> of the
> recordset object.
>
> Obviously I can just go and set up the base query for the
> recordset with the calculated fields and reference them, but I'm
> curious if anybody has any ideas on a code solution.
I see two solutions to this:
1. define your field as:
strField = "format([MyDate],"dd-mmm") As MyDate"
(you'll have to handle the nested quotes, of course; if you're
assigning strField from a value stored in a table, it won't matter,
of course)
2. keep track of how many fields your are adding in your dynamically
constructed recordset, and refer to them by index. If the field
defined above is the 3rd one in the SELECT clause of your recordset,
you could refer to it as:
rst(2)
because it's a zero-based index (rst(0) is the first field, rst(1)
is the second, etc.).
But, again, you'd then have to know how to connect the field to the
index number, and that might be completely impossible in the context
you're talking trying to use it.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
.
- References:
- Evaluating Complex Recordset Field Statements
- From: Kevin Rollo
- Re: Evaluating Complex Recordset Field Statements
- From: David W. Fenton
- Evaluating Complex Recordset Field Statements
- Prev by Date: Re: Tree view adding duplcate keys
- Next by Date: Re: Effects on RST in Calling Proc
- Previous by thread: Re: Evaluating Complex Recordset Field Statements
- Next by thread: Outlook mailitem
- Index(es):
Relevant Pages
|
