Re: Variabel fieldnames in queries

"R. Groot Beumer" <Info_at_RGBplus.nl> wrote:

>Hello (again),
>
>I have a question about the use of variabel fieldnames in queries. In a
>table of mine I've worked with cummulatives. The tables has got (among
>others) 13 fields which are named: cm_01, cm_01, cm02 etc (last one is
>cm_13).
>In a certain query I want to get the values of one of the above fields by
>using a variabel (perhaps a function). So the user is asked: "Which period
>do you want to export". The system must take the right field. In simple VBA
>this is done with: Me("cm_" & [period]) but how is this done within a
>query.
>
>As usual: any help is appreciated !!
>
>Thanx,
>
>Remco (Holland)

You could:
build the SQL string in VBA and then
create a new Query with CreateQueryDef and open it or
create a Report wich uses the SQL-String as Recordsource.

about CreateQueryDef have a look in the online help

quick & dirty example for Recordsource in Report:: ~~~
dim sSQL as string

sSQL = "Select  " & me("cm_"&[period]) 
sSQL = sSQL & " as FIELDNAME  from Tb_Nametable "
sSQL = sSQL & " where (("& me("cm_"&[period]) & ")= 100);"

docmd.openreport Reportname, acViewDesign

reports!Reportname.recordsource = sSQL

docmd.openreport Reportname, acViewPreview ~~~

In this case you must use as Controlsource for the variable Field the FIELDNAME (or FIELDNAMEs of course).

Hope it helps

-- 
Tommaso Cafini                  _tc_at_rhein-neckar.netsurf.de_
                                _tcafini_at_gmx.net_
PGP-KeyID       = C922A2B1 
Key fingerprint = 7C 9E 72 C5 11 06 A7 67  54 03 FF 02 4C 90 39 F5