Embedded Query throwing up unexpected results

[graeme34 via AccessMonster.com]

I am trying to embed the following query into my code:

strSQL = "SELECT * " _
& "FROM tblDespatch " _
& "WHERE SalesOrderNumber = " & lngOrderNum & ";"

Set rsDes = CurrentDb.OpenRecordset(strSQL, _
dbOpenDynaset)

It works fine, with this being a simple query it looks quite tidy. But when I
experimented to try and tied it up with the following:

strSELECT = "SELECT *"
strFROM = "FROM tblDespatch"
strWHERE = "WHERE SalesOrderNumber =" & lngOrderNum & ";"
strSQL = strSELECT & strFROM & strWHERE

It failed, could somebody please advise the correct syntax please.

 

[Someone]

If what you have given is literal, then you have not provided a space before
the last double quotes on each line.

This is what it would look like:

SELECT *FROM tblDespatchWHERE SalesOrderNumber =" & lngOrderNum & "

I presume this is the problem. That said, the first incaration of code you
used is usual - your second variation, I feel personally, doesn't make it
look any clearer.

M

 

[Marshall Barton]

I am trying to embed the following query into my code:

strSQL = "SELECT * " _
& "FROM tblDespatch " _
& "WHERE SalesOrderNumber = " & lngOrderNum & ";"

Set rsDes = CurrentDb.OpenRecordset(strSQL, _
dbOpenDynaset)

It works fine, with this being a simple query it looks quite tidy. But when I
experimented to try and tied it up with the following:

strSELECT = "SELECT *"
strFROM = "FROM tblDespatch"
strWHERE = "WHERE SalesOrderNumber =" & lngOrderNum & ";"
strSQL = strSELECT & strFROM & strWHERE

It failed, could somebody please advise the correct syntax please.

I like your first shot better than the "improved" version.

It's not working because you did not put a space at the end
of each piece the way you originally did.