Run-time error 3075 - Syntax error in string in query expression VB6/MS-access

[srinathvs]

Hi,
I have an access db that I am trying to query from a vb6 program. I've the following code:
Dim sSQLQuery As String
sSQLQuery = "SELECT * FROM TblData WHERE ID = " & Chr(39) & ID & Chr(39)
ID here is equal to 1234567890

MsgBox sSQLQuery
the msgbox says: SELECT * FROM TblData WHERE ID = '1234567890 Note that the quotation is missing at the end

Set rs = db.OpenRecordset(sSQLQuery, dbOpenDynaset)
Here I get the error:
Run-time error '3075'
Syntax error in string in query expression 'ID='1234567890'.
Note that now there's a single quotes before ID (+ where's the rest of the sql string).

As a note, ID is not a numeric field in the access database. Its in as a text-field. Now here's the biggie: this code works for me in my development PC and the query returns with the data in the db. This started happening on another laptop that I installed this application on all of a sudden and I am unable to reproduce this on my PC.

Any help is appreciated.

[debasisdas]

the following code

Dim sSQLQuery As String
sSQLQuery = "SELECT * FROM TblData WHERE ID = '" &  ID & "'"
 

it never happens that the same code will give different output on the same or any other system.

[srinathvs]

Hi.
Thanks for replying.

Chr(39) adds the quotation (') before and after ID. I also tried the quotation strings directly like you've suggested and i get the same result.

I agree that the same code will not give different results on different PCs. But, isn't it a possibility that my dev PC has some dependencies satisfied that a piece of code works alright on my PC, and the other PC is missing those dependencies. I don't think that is the case with this specific problem that I am having, but I just wanted to float that thought out.

[debasisdas]

Are you still getting the error after doing the way i suggested ?

[srinathvs]

It turned out that the ID that was returned from the source was null terminated and hence the quotation char could not be added.

Thanks for your responses.