DateDiff and SQL Statement

[JDU]

I have an input box (nbtInputBox) whose value I want to include in an SQL statement. It seems that the value is being evaluated properly from the inputbox; however, the value from the field in the table is not (see immediate window result below) Help.

strGetFireSafety = "Select * from tblCompetencyData where txtLogin = " & Chr(34) & rsGetName!txtLogin & Chr(34) _
& " and txtProcedureNo = 'MAN-001'" _
& " and datediff('yyyy',tblCompetencyData!nbtEvalDate," & nbtInputBox & &quot = 0"

Set rsGetFireSafety = db.OpenRecordset(strGetFireSafety)

Immediate Window:
?strGetFireSafety
Select * from tblCompetencyData where txtLogin = "CB415" and txtProcedureNo = 'MAN-001' and datediff('yyyy',tblCompetencyData!nbtEvalDate,1/1/00) = 0

 

[RobertT687]

You need to surround the last argument (date) with #:
try this as the last line:
& " and datediff('yyyy',tblCompetencyData!nbtEvalDate,#" & & nbtInputBox & "#) = 0"

 

[vbajock]

I thought we had this one. What is "nbtInputBox" ? Is it a field in the table, or a variable in your program ?

 

[JDU]

Thank you so much, that seemed to fix the problem.

I didn't even think to put # in front of the variable nbtInputBox because I had defined it as a date variable. Can somebody explain why even when you define a variable as date, you still need to enclose the variable in # #.

Thanks

 

[vbajock]

That defines a variable as a date to the SQL processor, just like the ' or " defines a string to it. If it knows it is a date, it can call built in functions to do date arithmetic on the value.

 

[JDU]

I understand what you are saying. What I mean is this; in my declarations I declared

Dim nbtInputBox as Date

Why then the need for #nbtInputBox# in the SQL statement.

Thanks