WHERE f$

J

JethroUK

How do i pass a global variable (as criteria) to a Query design/SQL?

The variable f$ will contain an sql where statement e.g. "[Enquired] Is Not
Null AND [Letter Sent] Is Null"

Works fine if i just copy/paste into the SQL statement, or criteria line in
design

But I have to pass it as a variable (it will change according to a form
filter)

It has to be part of a query design because this in turn is used by a
mail-merged letter
 
R

Rick Brandt

JethroUK said:
How do i pass a global variable (as criteria) to a Query design/SQL?

The variable f$ will contain an sql where statement e.g. "[Enquired] Is Not
Null AND [Letter Sent] Is Null"

Works fine if i just copy/paste into the SQL statement, or criteria line in
design

But I have to pass it as a variable (it will change according to a form
filter)

It has to be part of a query design because this in turn is used by a
mail-merged letter
Click to expand...

You'll have to use code to change the SQL property of the query on the fly prior
to running the merge.

CurrentDB.QueryDefs("QueryName").SQL = "Base SQL " & f$
 
J

JethroUK©

This has been paining me for weeks - Thankyou


Rick Brandt said:
JethroUK said:
How do i pass a global variable (as criteria) to a Query design/SQL?

The variable f$ will contain an sql where statement e.g. "[Enquired] Is Not
Null AND [Letter Sent] Is Null"

Works fine if i just copy/paste into the SQL statement, or criteria line in
design

But I have to pass it as a variable (it will change according to a form
filter)

It has to be part of a query design because this in turn is used by a
mail-merged letter
Click to expand...

You'll have to use code to change the SQL property of the query on the fly prior
to running the merge.

CurrentDB.QueryDefs("QueryName").SQL = "Base SQL " & f$
Click to expand...
 
P

Pat Hartman\(MVP\)

You can create a function that returns the variable value:
Public Function YourFunc()
YourFunc = f$
End Function

Select .....
From ...
Where YourField = YourFunc();

I prefer to use hidden form rather than global variables. So, in that case
the query would be:
Select ....
From ...
Where YourField = Forms!frmHidden!YourField;
 
Top