Using a variable in a SQL Query

[rickgalty]

Hi, I'm getting a "data type mismatch" error when trying to use a variable in the "WHERE" clause of a SQL "SELECT".

The code line that fails is :-

("SELECT * FROM Employees WHERE EmployeeNo = '" & empno & "'")

empno is a variable declared by "Dim empno As Integer", and the field "EmployeeNo" is of type "Long Integer" in the Access Database.

If I use the line ("SELECT * FROM Employees WHERE EmployeeNo = 1014") instead, for example, the program works fine. The variable 'empno' is being filled properly - I put in a text box and a line "Text1.Text = empno" and the value "1014" appears in it at the correct time.

Do I have a syntax error or something? Why the mis-match?

Thanks, Richard

 

[HarleyQuinn]

With numbers you don't need the 's. You could try using:

("SELECT * FROM Employees WHERE EmployeeNo = " & empno)

That syntax should produce the SQL string:

SELECT * FROM Employees WHERE EmployeeNo = 1014

This string (as you mentioned in your above post) should be the one you are looking for.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[rickgalty]

You, sir, are a Gentleman and a Scholar... that did the job. Thanks a lot.

Richard