How to use Variable in Criteria of Query

[pcdaveh]

I have a variable that I have read into a global variable. I now want to use that variable in the criteria of a Query SQL statement. How do I accomplish this task?

 

[pcdaveh]

Here is an example of the SQL Statement

SELECT tbl_PartnershipMembers.Account
FROM tbl_PartnershipMembers
WHERE (((tbl_PartnershipMembers.Account)="MyGlobalAccountNumber&quot);

 

[bimmer]

Write a function to get the value of your Global Variable.
Eg
Global MyGlobalAccountNumber as String
MyGlobalAccountNumber = "ABCDEFG"

Function mGetGlobAccNumber() as String
mGetGlobAccNumber = MyGlobalAccountNumber
End Function

In the QBE for your query, place the following in the Criteria: section for your tbl_PartnershipMembers.Account field:

=mGetGlobAccNumber()

 

[JimHorton]

pcdaveh,
The bimmer solution works well, but if you don't want to use globals, which can be dangerous, you'll have to just dynamically alter the sql of the query. Ie, if you had a static query, meaning all the same select fields and joins were not going to change, you could just get the sql from the querydef in code, use Instr() to find the Where clause, trunc the sql at that spot, then add your where clause using the variable local to the procedure you're in, and reset the querydefs.sql to the new sql
--Jim

 

[pcdaveh]

Thanks for the Responses!