How to put global variable name in query

_{Sunday, July 17, 2011 12:29 AM}

I set global name (LoginID) in my module and want to reference it for criterior in query design "SELECT A, B FROM TableX WHERE C=LoginID"

I try to use '" & LoginID & "' but it was not working.

How can I reference it?

_{Sunday, July 17, 2011 1:28 AM ✅Answered | 3 votes}

It's not possible to use visual basic variables directly inside of a query that's going to be run by the database engine (they are seperate systems).

However SQL query are able to consume public functions that you create in VBA.

So, a solution is to wrap a public function around a variable.

That function returns the variable value, and that function therefore in effect gives you the same round about solution. So, public VBA functions can be used in sql query.

Eg:

Public Function lngLogOnID as long

  lngLogOnID = LogOnID

End Function

So assuming the above public function is placed in a standard code module, then

SELECT A, B FROM TableX WHERE C = lngLogOnID()

Of course if you doing this inside of code, then probably you'll do something like this

Dim strSql  as string
Dim rstRecords as dao.RecordSet

strSql = "SELECT A, B FROM TableX WHERE C = " & LogOnID
str rstRecords = currentdb.OpenRecordSet(strSql)

So, obviously when working with VBA code, you don't need this ability, because you can evaluate the expression your code and build the SQL string.

Albert D. Kallal  (Access MVP)
Edmonton, Alberta Canada

_{Sunday, July 17, 2011 7:37 AM}

I set global name (LoginID) in my module and want to reference it for criterior in query design "SELECT A, B FROM TableX WHERE C=LoginID"

I try to use '" & LoginID & "' but it was not working.

How can I reference it?

Hi NovaVuth,

Besides functions, you can also "concatenate" the value of a variabele in your SQL-string. In fact you did that already. It did not work however because LoginID probably is a Long, and then you may not use the single quotes (that is only for text).

So,

 SQL_stirng =  "SELECT A, B FROM TableX WHERE C = " & LoginID

should work.

Imb.

_{Monday, July 18, 2011 4:15 AM | 1 vote}

Hello NovaVuth:

Albert Kallal correctly posted that SQL statements can include calls to public functions.  As he mentioned, if you are using the Query Builder, you can't include Global variables, but you CAN include functions that return the value of the global variables.

There are two ways to accomplish using Globals in queries:
 (1) Construct the query in VBA, where you CAN use the globals.
 (2) Use the query builder and include calls to functions that return the value of the variables.

For an example of the second method (which seems like the one you are using) see the following description:

http://www.logicwurks.com/CodeExamplePages/ADynamicQueries.html

If you are building the SQL in VBA, then you can use the examples in this link:

http://www.logicwurks.com/CodeExamplePages/ACreateVBA_SQL_Statements.html

Regards,

Rich Locus Logicwurks, LLC www.logicwurks.com