Need some help with SQL

[Kango]

Hi People,
I have some code running the following SQL statement.

rst.Open "SELECT tblDaysOff.VacDate, 11 AS Colr, tblDaysOff.staffid " & _
"FROM tblDaysOff WHERE (((tblDaysOff.staffid=" & Chr(39) & Me.cmbEmpName.Column(1) & Chr(39) & ") AND " & _
"(Year([vacdate])= " & Chr(39) & lblYear & Chr(39) & "))) " & _
"ORDER BY tblDaysOff.VacDate;", CurrentProject.Connection, , , adCmdText

The table called tblDaysOff as a field called staffid. When the datatype is set to Text the code runs fine, but i need the Staffid to be a number field. When i change the datatype of the staffid field to number i get an "data type mismatch in criteria expression" .

I guess the error is because the staffid has changed from text to number and therefore the quotes need to be removed, but i am not sure where i need to remove the quotes. Can somebody help ....


Thanks

Kango



The t

 

[Uncle Gizmo]

I believe you need to remove both of the Chr(39) from this section:

WHERE (((tblDaysOff.staffid=" & Chr(39) & Me.cmbEmpName.Column(1) & Chr(39) &

 

[Kango]

thanks...

Hi there,

Thanks for that, it worked ......... Very appreciated....




Kango