WHERE f$

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JethroUK - 19 Nov 2005 13:01 GMT

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

Reply to this Message

Rick Brandt - 19 Nov 2005 13:37 GMT

> How do i pass a global variable (as criteria) to a Query design/SQL?
>
[quoted text clipped - 9 lines]
> 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$

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

This has been paining me for weeks - Thankyou

> > How do i pass a global variable (as criteria) to a Query design/SQL?
> >
[quoted text clipped - 19 lines]
> to this message. Send instead to...
> RBrandt at Hunter dot com

Reply to this Message

Pat Hartman(MVP) - 21 Nov 2005 05:35 GMT

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;

> How do i pass a global variable (as criteria) to a Query design/SQL?
>
[quoted text clipped - 9 lines]
> It has to be part of a query design because this in turn is used by a
> mail-merged letter

Reply to this Message