rerunning same query, different field name...

[kerrigirrl]

this can't be that hard, but i'm out of ideas!

in my table i have:
jobnum
date
qty
elem1
elem2
...
elem11

i want my query to append to a new table:
jobnum
date
qty
elem

instead of creating 11 different queries (one for each elem), i want to be able to rerun the same query and just change the field from elem1 to elem2 to elem3 and so on.

so i created a textbox on the form that contains the field name (elem1...) and this changes to the correct field name before running the query. but it actually appends the FIELD name, not the value.

how do i get the query to look at the textbox value as the field name to be used in the query???

 

[MichaelRed]

I would use a parameter query, with the elem1 ... elem 11 as the parameter for the elem field. no need for the textbox, you can just construct the parameter value from a loop index:

For Idx = 1 to 11
[tab]MyPArm = "elem" & Left("00" & Trim(strng((Idx))
[tab][tab][tab].
[tab][tab][tab].
[tab][tab][tab].
[tab]'the rest of the query stuff, set the param, execute ..
Next Idx


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[kerrigirrl]

i need to use it in multiple calcs, i can't comprehend how this would work with a parameter query...