WHERE f$

[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

 

[Rick Brandt]

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

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$

 

[JethroUK©]

This has been paining me for weeks - Thankyou

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

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$

 

[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;