A query that can references a different Field 1

[psarros]

Hello

I have a query that I need to run several times but one field, [FACTOR] needs to change every time the query is run.

Is there a way to pass a variable into a query that will change the field it references.

The first time I run it I want factor to be PE, the next time CF the next Earnings.

I tried using the IIF statement but the query would need to change each time a factor is added.

Is there a way to pass a variable directly into the query for the field name.

THANKS IN ADVANCE
for your help

 

[lupins46]

Use a function to return the relevant field value to include. Use a Public varaiable to tell the function which field should be returned.

So in the query you have a column which is:

MyField:getvalue(PE,CF,Earnings)

In a module you have:

Public WhichField as integer

Function GetValue(vPE, vCF,VEarnings)

if WhichField = 1 then
GetValue = vPE
else if whichfield= 2 then
getvalue = vCF
else
Getvalue = VEarnings
end if
end function

You need a line of code added to the button or procedure that runs your query which sets the value of WhichField.

Having said all this I really don't see why 3 separate queries would not be so much easier to manage.

 

[psarros]

Thanks

This works and will save me some headache.

It is actually about 9 different fields so now I have one query in place of 9 update queries.