public variable as criteria in a query

[northernbeaver]

I have a huge report where I will have to run a macro multiple times that calls about 10 queries that will all be referencing one variable (I want to set it up as a variable as I will need to change it and re-run the macro) any ideas on how to set up the public variable so the querries know that its a variable and not text?

 

[zevw]

create a little table or form that has only one field

In the criteria in your report query say where the field equals the value of the table or form field.

Remember if you reference it to a form it most be open when you run your reports.

 

[CajunCenturion]

You could also put that value in a hidden text box in your main form, and have the queries reference the value in the textbox.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[cmmrfrds]

A query can use a Function, so return the public variable in a function.

public var as integer

Public Function ReturnVar() as integer
ReturnVar = var
End Function

Select * from tab where myfield = ReturnVar()

 

[northernbeaver]

Great guys its working. I used the public variable and calling a function, thank you very much, but another problem has arisen which will be hard for me to explain, I need to reference the value of the function in the field part of a querry

BOMSUNITS: [tbl BOM Sub Master]![SUnits]*GetBOMQTYNeeded()

is what I attempted but does not seem to work
any ideas?

 

[cmmrfrds]

Make sure that a value is being returned from the function at the time you are using it.
debug.print GetBOMQTYNeeded()

If the function is returning a NULL or Zero that will be a problem the way you are using it.

 

[northernbeaver]

I ended up doing via VB code instead of Querry, since it couldnt accept the field math I was trying. if anyone can tell me how it should have been done I would still like to know. thanks for all your input