reference a VBA variable in a Select Query Criteria

[Labadore]

If anyone knows the syntax to, say compare, a variable in the VBA code with a field value in a table using a SELECT Query please write me back and let me know. I'm not sure if it is possible to even reference that variable in a query. Any info will help. Thank you in advance.

 

[MisterC]

Create a VB function to return the value of the variable -
lets say you call it "MyVar". You then reference the function within your query, i.e.

SELECT * FROM tblWhatever WHERE tblWhatever.field = MyVar;

 

[Labadore]

Thanx MisterC for your response. Usr is the VBA variable and I'm trying to match it in this query with UsrName. When there is a match I want all fields of that user in table TimeSchedule to show when UsrName = Usr. Usr is their login name when they logon to windows through the api. Primary key is EmpNo if that makes a diff. I ran this query without the EXISTS and error told me to use EXISTS. I included EXISTS and error told me I can only select one field. If you or anyone knows what is going wrong here please write me back asap. Thanks again in advance.


(SELECT*FROM[TimeSchedule]WHERE EXISTS([EmpMatch].[UsrName] = Usr)

 

[MisterC]

Try this:

SELECT * FROM TimeSchedule
WHERE TimeSchedule.UsrName = Usr();

I assume Usr() is the name of the function you have created to return the value of your variable. Hope that helps