Global Variable in Paramater Query (1 Viewer)

[Capilano]

I have declared a Global Variable in my application. I am having trouble using this Global Variable called "vTargetClient" as the criteria in a Select Query. I have tried using the following syntax but to no avail.

" & vTargetClient & "

Any help would be appreciated.

Thanks

Pat

 

[Pat Hartman]

Variables are VBA objects and cannot be used directly by SQL. You can create a function that returns the variable and use the function in SQL.

Public Function YourFuncName()
YourFuncName = vTargetClient
End Function

Then in the where clause of the query:

Where SomeField = YourFuncName();

 

[Capilano]

Thanks for the advice. However, I guess I should be more clear. I have successfully used the CreateQueryDef in a Public Function to create underlying queries for my Forms. The Function knows what queries to create or correct SQL Statement because I have the entire SQL syntax in a Table. I then open a Recordset and call the SQL Statement and create the Query Def:

Dim db As DAO.Database, rec As DAO.Recordset, qry As DAO.Querydef
Set db = CurrentDb()
Set rec = db.OpenRecordset("zt_QueryDefs")

I then loop through and create all of the queries that I want to create.

rec.MoveFirst
Do Until rec.EOF
Set qry = db.CreateQuerydef(rec![Table_Name])
qry.SQL = rec![Table_Path] & " ='" & vTargetClient & "'));"
rec.MoveNext
Loop

I DO realize that the Global Variable (vTargetClient) in the "Where" statement is declared sloppily using the above syntax. However, if I have the complete SQL Statement in the Table, (including vTargetClient as the criteria), it will not call it properly. It SHOULD read like the following:

Like the following:
qry.SQL = rec![Table_Path]
provided the Where section is included in the complete SQL statement which is inside the Table. Unfortunately, it does not work. Rest assured that the Global Variable works and is set earlier in the Function.

Pat

[This message has been edited by Capilano (edited 01-28-2002).]

 

[Pat Hartman]

The logic of what you are doing escapes me. You could have simply used stored querydefs that took parameters. They would have taken no code and been more efficient to boot.

Have you used the debug window to examine the value you are placing in the SQL property? There may be a syntax error.

 

[Capilano]

Hi...

Logic: This procedure is as 'generic' as possible so as to use the same process of creating queries in any number of different databases. While the SQL Statement may change, the criterion is always based on the value of the Global Variable. It is a modularized approach to help minimize the developement. Simply stated, one single Table in each of the varying databases holds the SQL statements that are necessary for creating the queries for that particular database. Rather than writing QueryDefs for each and every database, I simply modify the SQL Statements in the particular Table. A 'single' procedure can then call the 'customized' SQL statements and create the necessary queries in any one of my 10 databases. (If you have a better way... I am willing to listen.)

Why do it this way? Experience has proven that "Select Queries" run much faster when the exact criterion (in this case, Client_No) is on the criterion line as opposed to basing the criterion on a Field on a Form. Eg.

SELECT tb_TableName.Client_No FROM tb_TableName
WHERE (((tb_TableName.Client_No)='631107547'));

runs MUCH faster than...

SELECT tb_TableName.Client_No
FROM tb_TableName
WHERE (((tb_TableName.Client_No)=[Forms]![FormName]![txtFieldName]));

A Client is selected in a Dropdown Box, and the value of the Client is then set to a Global Variable. This value can then used throughout the database. In this case, I want the Global Variable to become the "Where" criterion for the Select Query (or a complete SQL Statement) in the Table.

The following SQL Statement when called from the Table using the function WORKS. It creates the desired Query.

SELECT tb_TableName.Client_No FROM tb_TableName
WHERE (((tb_TableName.Client_No)='631107547'));

The following SQL Statement when called from the Table does NOT work.

SELECT tb_TableName.Client_No FROM tb_TableName
WHERE (((tb_TableName.Client_No)='" & GlobalVariableHere & "'));

Yes, I have checked the Debug Window and the Syntax looks correct. It creates the Select Query but the syntax in the Criterion remains '" & GlobalVariable & "' not '631107547' Consequently, it returns no value(as you so correctly pointed out at the very begining..."Variables are VBA objects and cannot be used directly by SQL...")

Hope this helps.

Pat

 

[Rich]

I've just tested your comments on Experience has proven that "Select Queries" run much faster when the exact criterion (in this case, Client_No) is on the criterion line as opposed to basing the criterion on a Field on a Form. on a linked table with over a million records, I couldn't detect any difference, could there be a flaw in your approach?