Evaluating Complex Recordset Field Statements

[Kevin Rollo]

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.

--
Regards,
Kevin

[MarcHG]

Kevin,

the expressions can be used as part of the rst.sql string varible as
"<expression> as <name>" and referred to by name in the
rst.fields("<name>") expressions. You can construct the rst.sql string
from application-parameters and refer to the fields by their name. Even
expressions using your own public VBA-fnctions using parameters
referring to constants or fields from the database can be inegrated in
the rst.sql string. When redefining existing or already opened
recorsets a rst.requery will be needed to get the results.

Marc

[David W. Fenton]

"Kevin Rollo" <kr*******@hotmail.com> wrote in
news:95******************@news-server.bigpond.net.au:

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

[Kevin Rollo]

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" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...
"Kevin Rollo" <kr*******@hotmail.com> wrote in
news:95******************@news-server.bigpond.net.au:

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