WHERE clause in SQL string

[Carl Foster]

Hi all, do you think you could help me with this SQL statement I'm using in vba code for a recordset object?

strSQL = "SELECT idCompactPartNumber, txtItem," & _
"lngDepartmentNumber , dteAquiredDate, curPrice," & _
"intQuantity , lngUnit, txtLocation," & _
"imgPicture, dteRetiredDate, blnActive," & _
"blnConsumables " & _
"FROM tblMaterials" & _
"WHERE lngDepartmentNumber=" & lngDepartment

When I try to open the recordset with this, it says there is an error in the WHERE clause. I've tried changing all sorts of things but nothing works. I've tried with the WHERE clause commented out and it all works fine then.

Hope you can help, thanks

 

[WayneRyan]

Carl,

"WHERE lngDepartmentNumber=" & lngDepartment

Given the above, it's probably one of two things:

a) There is no field called lngDepartmentNumber in your table

b) there is no value for lngDepartment giving --> Where lngDepartmentNumber =

Wayne

 

[Carl Foster]

Carl,

"WHERE lngDepartmentNumber=" & lngDepartment

Given the above, it's probably one of two things:

a) There is no field called lngDepartmentNumber in your table

b) there is no value for lngDepartment giving --> Where lngDepartmentNumber =

Wayne

Thanks for your reply.
As you can see though, there is a field lngDepartmentNumber shown in the SELECT part of the statement. Also, the variable lngDepartment does receive a number during runtime. I've even substituted the variable for a number and it still gives an error

 

[EMP]

"FROM tblMaterials" & _
"WHERE lngDepartmentNumber=" & lngDepartment

You need to have a space between the table name and the word WHERE.

^

 

[WayneRyan]

Good catch EMP.

Wayne

 

[Carl Foster]

Thanks, yes that is the problem.

Thanks very much