SQL syntax using WHERE with two variables, assistance needed.

[Edward_]

Hello all

I'm struggling with finding the proper syntax to use two variables with WHERE.



Using the actual values the SQL string works perfect:

iSQLStr = "SELECT * FROM tblData1  WHERE tblData1.Card = 1 AND tblData1.exam= 6 ;"

But when I attempt to substitute variables for the values I receive errors (on execution) with every variation I test:

crd = 1, exam = 6
iSQLStr = "SELECT * FROM tblData1  WHERE tblData1.Card=  & crd AND tblData1.exam= & exam ;"

Any suggestions greatly appreciated.

[IrogSinta]

Try this:
iSQLStr = "SELECT * FROM tblData1 WHERE tblData1.Card=" & card & " AND tblData1.exam=" & exam

[June7]

General guideline is: don't put variables within quote marks - concatenate variables.

If the fields are text type, they need apostrophe delimiters, if date type use #. Example showing text, date, number types:

SELECT * FROM tblDate1 WHERE Card = '" & Me.Card & "' AND ExamDate = #" & Me.ExamDate & "# AND Exam = " & Me.Exam

[Edward_]

Thanks to all.
The number type rule clinched it.

This is my result

iSQLStr = "SELECT * FROM tblData1  WHERE tblData1.Card= " & crd & " AND tblData1.exam=" & exam & ";"

[IrogSinta]

You didn't need to add the semicolon at the end by the way.

[June7]

Are you sure IrogSinta? I think it may be needed for a SELECT statement and I should have included in my example.

[IrogSinta]

I'm positive about the semicolon. I make it a point not to include that in any SQL string in vb code, that way I can append any additional criteria needed. For instance:

Dim sSQL as string

sSQL = "Select * From SALES Where Status='O' "
If chkDelivered = True then
     sSQL = sSQL & "AND [Deliv] = True"
ElseIf chkReturned Then
     sSQL = sSQL & "AND [Returned] = True"
End If

[June7]

Okay, good to know. I always assumed it was needed because Access query builder requires it.