Access Table Filtered based upon Excel User Input

[JWalcott]

I recorded a macro Importing External Data from Access and specifying in Microsoft Query that I only want employees that work for Client '0001'. No problem there. But when I try to do the same query where the user types in the Client Number = '0001', the input is stored publicly as a variant data type ClientNum = '0001', the sub procedure obtaining the data from Access returns only the column headings, no data!!
Hoping this is just a data type misunderstanding, any help would be appreciated. Sample of code obtaining results followed by the code returning no data.

"SELECT Employees.Participant, Employees.SocialSecurityNumber, Employees.FirstName, Employees.LastName, Employees.Title, Employees.Birthdate, Employees.StartDate, Employees.Salary, Employees.`HCE/Key`," _
, _
" Employees.ClientNumber" & Chr(13) & "" & Chr(10) & "FROM `C:\My Documents\db1`.Employees Employees" & Chr(13) & "" & Chr(10) & "WHERE (Employees.ClientNumber='0001')" & Chr(13) & "" & Chr(10) & "ORDER BY Employees.`HCE/Key`" _
)

' NO RESULTS CODE
"SELECT Employees.Participant, Employees.SocialSecurityNumber, Employees.FirstName, Employees.LastName, Employees.Title, Employees.Birthdate, Employees.StartDate, Employees.Salary, Employees.`HCE/Key`," _
, _
" Employees.ClientNumber" & Chr(13) & "" & Chr(10) & "FROM `C:\My Documents\db1`.Employees Employees" & Chr(13) & "" & Chr(10) & "WHERE (Employees.ClientNumber='ClientNum')" & Chr(13) & "" & Chr(10) & "ORDER BY Employees.`HCE/Key`" _
)

JW

 

[Ziggy]

You can set up a parameter query by putting a question in square brackets in the criteria of the field (MS Query Design grid).

Replace your criteria "ClientNum" with

[Enter Client Code]

Would be a Question mark in SQL

..."WHERE (Employees.ClientNumber= ? )" ...

Works just like Access

Try this instead of the Input box you may be using, When you run the Macro the user will be prompted .