SQL Statement

[JDU]

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

Set rsGetFireSafety = db.OpenRecordset(strGetFireSafety)

In the above code,I keep getting the error "Too few parameters, expected one". It seems that it is not recognizing the value(date) in the field nbtEvalDate. nbtInputBox is an input box whose value(date) is being recognized. Can someone help me correct this please.

Thanks

 

[Creepers]

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

Set rsGetFireSafety = db.OpenRecordset(strGetFireSafety)

Added a space before the and

 

[JDU]

It didn't seem to solve the problem

 

[Creepers]

Char(34) = "
You are probable looking for '
If so just use ' on the left side of "
Like this...

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

 

[JDU]

This is what I have.

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

I know that the problem is in the last line because if I comment it out, I don't get an error.

 

[vbajock]

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

PUT A STOP OR CONTROL BREAK HERE, GO TO THE IMMEDIATE WINDOW, TYPE ?strGetFireSafety, REPLY BACK AFTER CUTING AND PASTING RESULTS OF ? IN A REPLY WINDOW

Set rsGetFireSafety = db.OpenRecordset(strGetFireSafety)

 

[JDU]

Here you go:

?strGetFireSafety
Select * from tblCompetencyData where txtLogin = "CB415" and txtProcedureNo = 'MAN-001' and datediff('yyyy',nbtInputBox,nbtEvalDate) = 0

 

[vbajock]

Your variables nbtInputBox,nbtEvalDate are being interperted as part of the string

Make sure your variables nbtInputBox,nbtEvalDate are formated as dates.

SELECT tblCompetencyData.txtLogin, tblCompetencyData.txtProcedureNo," +((DateDiff("yyyy",nbtInputBox,nbtEvalDate))+" AS Expr1, *
FROM tblCompetencyData
WHERE (((tblCompetencyData.txtLogin)='CB415') AND ((tblCompetencyData.txtProcedureNo)='MAN-001') AND ((0)=0));


Why are you using Chr(34) ? If you are trying to process a string within a string use a single quote within a double. Sql sees the single quote as if it were a double quote. If you actually have passwords that are literally "CB415" yours is correct.

 

[vbajock]

There is a typo in what I sent you; fixed here:

SELECT tblCompetencyData.txtLogin, tblCompetencyData.txtProcedureNo," +((DateDiff("yyyy",nbtInputBox,nbtEvalDate))+" AS Expr1, *
FROM tblCompetencyData
WHERE (((tblCompetencyData.txtLogin)='CB415') AND ((tblCompetencyData.txtProcedureNo)='MAN-001')

typo
AND ((0)=0));
should be
AND ((Expr1)=0));