SQL syntax error - HELP PLEASE 3

[datavisions]

I'm getting a syntax error on the SQL below now and after staring at it for FIVE hours now I'm about to go mental. Can someone help me please?

Public Function ParseAndStoreAADetailRecord(GFile)
On Error GoTo Err_Handler

Dim db As Database

Dim AN As String
Dim RC As String

Set db = DBEngine(0)(0)

AN = Mid(GFile, 1, 12)
RC = Mid(GFile, 107, 30)

db.Execute "INSERT into tblAAAccidentTemp(" _
& "OldAccountNumber, " _
& "RejectCode)" _
& "VALUES (" & AN & _
"," & RC & ""

End Function

 

[scriverb]

I think you just missed a space that was necessary between "Rejectcode)" and "VALUES (". You see when cancatenated as you had it it would look like this:
"Rejectcode)VALUES ("

By adding a space in front of the word VALUES the SQL string can be interpreted correctly.

db.Execute "INSERT into tblAAAccidentTemp(" _
& "OldAccountNumber, " _
& "RejectCode)" _
& " VALUES (" & AN & _
"," & RC & ""


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[datavisions]

Thanks Bob: I tried adding the space to after rejectcode), I tried adding it to before Values, I've tried EVERYTHING and I'm going mental here. I'll PAY someone to get this to work at this point!!!!!!

 

[datavisions]

Can anyone please fix this syntax for a reasonable fee? At this point, I will pay for it, as I'm TOTALLY stuck in my tracks here.

 

[scriverb]

No need for payment. But, buy me a beer someday if I ever travel to your city.

I created a table with the fields as described, ran the code and the record is inserted properly. No errors.

Please describe the errors that you are experiencing. Let's try putting dim db as DAO.Database in place of your statement. Put a STOP just in front of the db.execute statement. Then put your cursor over the two variables and see what values are being selected with the Mid function.

Waiting for your response.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jiqjaq]

I think the problem is that you are trying to use string values (RC, AN) in the VALUES clause, but those strings are not enclosed within quotes. Without the quotes SQl won't know what the text string represents (unless it's defined as a local SQL variable).

Whenever you need to concatenate in code to form a SQL statement, it's best to assign the results first into a string variable, then execute using that variable. When it doesn't work (which for me is a given the first few times), you can print out the string to see what you're executing.

strSQL = "INSERT into tblAAAccidentTemp(" _
& "OldAccountNumber, " _
& "RejectCode)" _
& "VALUES ('" & AN & _
"','" & RC & "');"

db.Execute strSQL

 

[scriverb]

jiqjaq, is correct with the single quotes. Mine was working because the example I used was all numeric numbers and ACCESS just inserted the strings into the SQL as a number and when the Insert took place and the numers were inserted into a Text field the reverse took place.

The single quotes are necessary to keep the values straight as strings or text. Way to go jiqjaq. Have a star.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[datavisions]

scriverb: I still get the SQL syntax error.

The field AN has numbers in it (which looks ok on the watch..it has quotes around it.

The field "RC" is blank on import, but it also appears like a proper text import with the quotes around it

 

[datavisions]

jiqjaq : I had tried that as well, since that's the way I used to always do my SQL. It still didn't work. That's why I've been going mental over this today. Nothing seems to work.

 

[scriverb]

Are you saying that the

RC = Mid(GFile, 107, 30)

returns a blank value or Null.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[datavisions]

scriverb : That's correct. It's like a "remarks" field. Sometimes it has text in it, and sometimes it doesn't.

 

[scriverb]

I changed my data to simulate spaces for the second variable and my string turned out like this and it works.

INSERT into tblTemp(OldAccountNumber, RejectCode) VALUES ('12526785623 ',' ');

Is this what yours looks like when you display the SQL string being executed?


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[datavisions]

scriverb: Yes that's what it looks like

 

[scriverb]

Then it should work. My code updates the table as expected. Is yours as well. If not what is the error.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[datavisions]

scriverb: I'm not getting a syntax error any longer, BUT nothing it making it to the table. No message, nothing.

 

[scriverb]

Let's copy and post your code once again as there have some changes and I need to look at what you are trying to execute right now.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[datavisions]

I GOT IT! No syntax errors, and it INSERTS perfectly. Here's the full SQL (I had narrowed it down to two fields for simplicity earlier).

The trick is to put SINGLE quotes AROUND the double quotes, like:

'" & variable & "'

so here's my new code:

db.Execute "INSERT into tblAAAccidentTemp(" _
& "OldAccountNumber, " _
& "RecordType, " _
& "CertificateId, " _
& "ArrearsIndicator, " _
& "NewAccountNumber, " _
& "LockCode, " _
& "BillingDate, " _
& "RejectCode)" _
& " VALUES('" & AN & "','" & RT & "','" & CI & _
"','" & ARRI & "','" & NAN & "','" & LC & _
"','" & BD & "','" & RC & "');"

I can't thank everyone enough for their time and energy regarding this problem of mine! Thanks a million!

 

[scriverb]

Congratulations!!! No need to call in the paddy wagon. Where are you from so I can collect that beer?

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[datavisions]

scriverb: I'm in beautiful Toronto, Canada. Great selection of beers here.

 

[scriverb]

Love that city. Been there many times as my son(now 28 yrs) played hockey through juniors. I think I have been to every ice rink in North America including all the local rinks in Toronto.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???