Syntax error (Access 2000)

Topic

New Reply

Where have I gone wrong here?, I’m getting a Syntax error (Missing Operator) in query expression “3′-BL-T76206′, [REV]=’1”
Below is the code it points to (second line),,,thanks in advance..

sSql = “UPDATE [tblSheets] SET RefDWG='” & strRefDWG & “‘, [DocuNo]='” & strDocuNo & “‘”
sSql = sSql & “, ConstPart='” & strConstPart & “‘, [REV]='” & strRev & “‘”
‘sSql = sSql & ” WHERE RefDWG is Null;”
DoCmd.RunSQL sSql
End If

Reply | Quote

Replies

It looks like strConstPart contains a single quote (after the 3); since you use single quotes around string values, this confuses SQL.

There are several ways around this, but they will involve changing some of your code. Is there any chance that you can avoid having single quotes in your data? And in case you can’t, is it possible that any of your data will contain a double quote?

Reply | Quote

Thanks again Hans, removing the double quotes from around const part actually did the job. That simple modification now allows the code
to execute smoothly

Reply | Quote

ok, maybe I spoke too soon. It actually worked when I removed the double quotes, but not the second time around. Whats even more strange, when I restored it back to the original code
sSql = “UPDATE [tblSheets] SET RefDWG='” & strRefDWG & “‘, [DocuNo]='” & strDocuNo & “‘”
sSql = sSql & “, ConstPart='” & strConstPart & “‘,ConstUnit='” & strConstUnit & “‘, REV='” & strRev & “‘”
‘sSql = sSql & “, GetMod='” & Left(strconstunit, 7) & “‘”
sSql = sSql & ” WHERE RefDWG is Null;”
DoCmd.RunSQL sSql

I didn’t get the same syntax error message as before but this time I get an Invalid Sql Statement; Expected ‘Delete’,’Insert’,’,’Procedure’,’Select’, or ‘Update’
Also, the data is most likely to contain single quotes and doubles quotes.

Reply | Quote

Sorry Hans, apprarently, the data that these fields refer to does not have any any quotes or double quotes.

Reply | Quote

Post the zipped excel sheet you are trying to import.

Reply | Quote

Hi Pat,
This excel sheet is one of many. I also managed to modify the error trapping code for the last one and apply it to the first portion that we did .

Reply | Quote

I imported this sheet and I got a tblError entry that said:

Couldn’t find SHT No. or CUT SHT No. in heading

There is something that is different between my database and yours. Would you like to zip and post it.

Reply | Quote

Pat,
You know the very first database that you helped me with 3 weeks ago. Thats the one, I’ve utiilised last weeks code for that nightmare imporation scenerio, and applied it to the first one we’ve made. I stayed up all night studying the code.
The db is quite big, i can either email it to u, or paste the code here.
I say its quite strange, because it was working perfectly today morning.

Reply | Quote

All you ned to do is to delete all entries from tblSheets and compact the database.
This compresses it quite significantly then just zip it up. It should get to below 100k and to send in this forum.

Reply | Quote

Ok, i stripped it off all the graphics etc

Reply | Quote

I used the email version and I went into frmImport in design mode. I went into properties and chose the Import button code.
When you put code into forms or modules be sure that the 1st line after the Option Compare Database is a Option Explicit statement. This ensures that you define all variables. As soon as I did this and compiled the code it found a statement:
sSql = SQL & …..
The word SQL is undefined and should be renamed to sSql, then compile the code again. At this point you should get no errors.
Try testing the Import again after doing all this and it should work. I have tried it on mine and it works just great.

Reply | Quote

You know, I dont know how I managed to figure out the most complex problems last night, I was on a roll. And then comes this, I overlooked the the most basic cardinal rule of VB. Thanks Pat, now excuse me while I go hit my head against a wall.

Reply | Quote

Just remember, it feels great when you stop.

Really, I’m glad it was straight forward.

Reply | Quote

Actually, it feels better when the problem is resolved.
Just one more thing on this, I did import it, but it failed to pick up 4 excel sheets, these were the ones that were out of range and thus it was expected for it to have been logged into the errors table.
I’ve attached one of them.

Reply | Quote

>>these were the ones that were out of range and thus it was expected for it to have been logged into the errors table. <<
What do you mean by it was out of range?

At this stage I cannot fathom why the query won't work. I will keep looking at it.

Reply | Quote

Ok, maybe out of range was not a good way of putting it. But the excel sheet i attached previously refuses to be imported. And refuses to go into the errors table. This applies only to 4 excel sheets out of 335 that I’ve tested.

Reply | Quote

Here is the query that won’t work within the following instruction:
sSql = “qryUpdatetblSheets”
DoCmd.OpenQuery sSql

However, if you go into the database window and run the query from there, it works. Hmmmm, this is a strange one, can somebody please help.

The query follows:

INSERT INTO tblSheets ( ItemNo, QtyReqd, Description, MatlGrade, Length, [Length plus CTF], Width, WeightFactor, Weight, SAreaFactor, SArea, MrNo, FreeIssue, Remarks, SBMICostCodes, ExcelSheetName, [Size] )
SELECT [ImportExcel].[F1], [ImportExcel].[F2], [ImportExcel].[F3], [ImportExcel].[F4], [ImportExcel].[F5], [ImportExcel].[F5], [ImportExcel].[F7], [ImportExcel].[F8], [ImportExcel].[F9], [ImportExcel].[F10], [ImportExcel].[F11], [ImportExcel].[F12], [ImportExcel].[F13], [ImportExcel].[F14], [ImportExcel].[F15], ‘DSM13071_10of11_3004_R_HR1_Sht.xls’ AS Expr1, GetSize([F3]) AS Expr2
FROM ImportExcel
WHERE (((IsNumeric(Left([F1],2)))=True));

Reply | Quote

Shouldn’t … [ImportExcel].[F5], [ImportExcel].[F5] … be … [ImportExcel].[F5], [ImportExcel].[F6] … ?

Reply | Quote

Yes, I will have to see what’s wrong there.

Mark, you have changed the setting up of the description, or haven’t you put the code in that database yet?

Reply | Quote

Are u refering to the get size statement,,,yep, I’ve changed it as u suggested.

Reply | Quote

No, I was referring to the Description field, not the size field.

Reply | Quote

Your refering to the below right..?. If so, yep, its there.

sSql = sSql & ” F” & iRemarks & “,F” & iSbm & “, ‘” & strMatch & “‘,GetSize(F” & iDescription & “)”

Reply | Quote

No that’s the size. I was referring to setting up of the Description field which can be F3, F4, etc, etc.

Reply | Quote

If you mean the issue that Hans brought up, I did fix as such in the qryUpdatetblSheets
INSERT INTO tblSheets ( ItemNo, QtyReqd, Description, MatlGrade, Length, [Length plus CTF], Width, WeightFactor, Weight, SAreaFactor, SArea, MrNo, FreeIssue, Remarks, SBMICostCodes, ExcelSheetName, [Size] )
SELECT ImportExcel.F2, ImportExcel.F3, ImportExcel.F4, ImportExcel.F5, ImportExcel.F6, ImportExcel.F6, ImportExcel.F8, ImportExcel.F9, ImportExcel.F10, ImportExcel.F11, ImportExcel.F12, ImportExcel.F13, ImportExcel.F14, ImportExcel.F15, ImportExcel.F16, ‘PML-1262-M2-T76206-76.xls’ AS Expr1, GetSize(F4) AS Expr2
FROM ImportExcel
WHERE (((IsNumeric(Left([F2],2)))=True));

I’ve also checked one of the 4 suspect sheets. And it has no forumala calculation in the weight column.

Reply | Quote

ok, I’ve attached the 4 excel sheets. These are the “awesome” foursome that refuse to budge. All 335 that I’ve tested import smoothly.
At the moment, I’m trying to figure out this dillema by trying trial and error methods. So far, no positive fruits.
Interestingly, only one record from one of the excel sheets actually imports. DSM13071_10of11-3003_HR5-Sht.2.xls
Item No. 38
ELBOW

Reply | Quote

Sorry, let me rephrase what I said earlier, there is actually calculations in the weights field. But that is no different for the other pmls that imported smoothly.

Reply | Quote

No, ignore the formulas, I was clutching at straws. It’s ignoring the rows because of Validation Rule violations, and darned if I can see it. I will keep looking.

Reply | Quote

Anyway, I’m soooo tired, havn’t slept yet. I really have to learn to sleep early and get out into the sun more. Not so easy though with all this on top of me. If you’ve got any advice for my above question, then once again, a thousand appreciations Pat. Take care, speak to you later.
Mark the zombie is off to sleep zzzzzzzzzzzzzzzzzz

Reply | Quote

Happy zzzzzzzzzzz’s
I have changed your database to generate errors where the sheet could not be processed. I have also created a new query to go with it as well.
I have also changed the column for Free Issue to include CUT SHT No and SHT No as well, if this is not correct then just change the code back to how you had it.

Reply | Quote

Thanks a million Pat. It looks really good.
Nope, free issue and cutsht are not similar at all. Two different columns.
So I’ve changed it back accordingly.
I only had 3 hours of zzz’s,,,when u have something like this on your mind 24/7, then you’ll start having
dreams of it too,,,,aggghhh, gonna try and get more zzz’ s now

Reply | Quote

Guess what Pat? ,,,,,,,,,,I DID IT !!!
I’ve attached the working db, how i did it, i really am so tired to recollect, but it took me only 2 hours, of studying the code line by line.
Out of the 5 excel sheets, only 4 were logged into the errors table, and the other one was bypassed, but i’m suspecting it might have something to do with it being watermarked as Deleted, although thats just a rough guess.
Is there a simple way of identifying/matching the excel sheets in a folder, with those in a table, and then displaying the ones that are not in a table? (but happens to be in a folder). Is that possible?, I was thinking this might act as a last line of defence incase the errors table does not pick up the unsuccessful excel sheet.

I got an extension on this, mainly because the supervisor is away, so the deadline has been extended till tuesday tommorow when he gets back.
Mannn, I’m developing a positive relationship with access now, I guess I don’t need to curse it as much as before..
Woooohhoooo. You the Man Pat, your access skills make all my I.T. Uni Lecturers look like amateurs.
13 weeks in a semester and a student hardly learns anything in depth such as this.

Reply | Quote

Well, I get only 1 record (that you described ina prev post) to be imported into tblSheets, and I also got those 4 in the error table as lacking the column Free Issue. Those sheets have SHT No instead of Free Issue, are these columns similar?

Reply | Quote

In fact it does not work from the database window, it fails due to a validation rule violation.
What do we need to check here. I have checked for any validation rule violations in the table and there are none. I wonder if it could be a Null value that’s causing the problem.

Reply | Quote

As I mentioned to Hans in my correction, the data that these fields refer to does not have any any quotes or double quotes.
Incidently, these are the very first excel sheets that I ever dealt with when we began this importation saga.

Reply | Quote

I’ve noticed when clicking Import, and when the error message comes up, the qryUpdatetblSheets is deleted.
This is all strange, as it doesn’t explain why it worked fine one minute but not the next.

Reply | Quote

One of the first things that the import does is to delete that query. It is built “on the fly” because of the variances between sheets (eg, some sheets start in F1 or F2 or F3).

>>This is all strange, as it doesn’t explain why it worked fine one minute but not the next. <<
What works one minute but not the next?

Reply | Quote