SQL stopped working "reserved word" error

[tho28199]

Database was created with Access 2003, but is now being run under Office 2007.

Need some more eyes on this... it was working fine for several months, but now is failing with the error: "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.". The database has not undergone any changes.

The code that is failing is as follows:

strSQL = "INSERT INTO " & strTableName & " (BrokerName, EntryNumber, EntryType," & _
"EntryDate, EntryPort, BrokerInvoiceNumber, ConsigneeNumber, ImporterNumber," & _
"ExportCountry, OriginCountry, AirbillNumber, ManufacturerID, LineItem," & _
"SpecialProgramCode, TariffNumber, LineValueAmount, DutyAmount, MPFAmount," & _
"Brokerage, OtherCharges, Period, EntryOnFile)" & _
"SELECT TOP " & varSampleSize & " tblTemp.[BrokerName]," & _
"tblTemp.[EntryNumber], tblTemp.[EntryType]," & _
"tblTemp.[EntryDate], tblTemp.[EntryPort]," & _
"tblTemp.[BrokerInvoiceNumber], tblTemp.[ConsigneeNumber]," & _
"tblTemp.[ImporterNumber], tblTemp.[ExportCountry]," & _
"tblTemp.[OriginCountry], tblTemp.[AirbillNumber]," & _
"tblTemp.[ManufacturerID], tblTemp.[LineItem]," & _
"tblTemp.[SpecialProgramCode], tblTemp.[TariffNumber]," & _
"tblTemp.[LineValueAmount], tblTemp.[DutyAmount]," & _
"tblTemp.[MPFAmount], tblTemp.[Brokerage]," & _
"tblTemp.[OtherCharges], tblTemp.[Period]," & _
"tblTemp.[EntryOnFile]" & _
"FROM tblTemp " & _
"ORDER BY tblTemp.[RandomNumber];"

If I copy and paste the above into the SQL view of a query, replace the variables, and eliminate the puctuation, it works:

INSERT INTO tblCRKRandomSequence (BrokerName, EntryNumber, EntryType, EntryDate, EntryPort, BrokerInvoiceNumber, ConsigneeNumber, ImporterNumber, ExportCountry, OriginCountry, AirbillNumber, ManufacturerID, LineItem, SpecialProgramCode, TariffNumber, LineValueAmount, DutyAmount, MPFAmount, Brokerage, OtherCharges, Period, EntryOnFile) SELECT TOP 20 tblTemp.[BrokerName], tblTemp.[EntryNumber], tblTemp.[EntryType], tblTemp.[EntryDate], tblTemp.[EntryPort], tblTemp.[BrokerInvoiceNumber], tblTemp.[ConsigneeNumber], tblTemp.[ImporterNumber], tblTemp.[ExportCountry], tblTemp.[OriginCountry], tblTemp.[AirbillNumber], tblTemp.[ManufacturerID], tblTemp.[LineItem], tblTemp.[SpecialProgramCode], tblTemp.[TariffNumber], tblTemp.[LineValueAmount], tblTemp.[DutyAmount], tblTemp.[MPFAmount], tblTemp.[Brokerage], tblTemp.[OtherCharges], tblTemp.[Period], tblTemp.[EntryOnFile] FROM tblTemp ORDER BY tblTemp.[RandomNumber];

Can anyone see what I am obviously missing?

 

[boblarson]

Looks like you were missing two spaces. See the ones I added in between the red characters:

[FONT=Times New Roman][SIZE=3]strSQL = "INSERT INTO " & strTableName & " (BrokerName, EntryNumber, EntryType," & _
"EntryDate, EntryPort, BrokerInvoiceNumber, ConsigneeNumber, ImporterNumber," & _
"ExportCountry, OriginCountry, AirbillNumber, ManufacturerID, LineItem," & _
"SpecialProgramCode, TariffNumber, LineValueAmount, DutyAmount, MPFAmount," & _
"Brokerage, OtherCharges, Period, EntryOnFile[B][COLOR=red]) "[/COLOR][/B] & _
"SELECT TOP " & varSampleSize & " tblTemp.[BrokerName]," & _
"tblTemp.[EntryNumber], tblTemp.[EntryType]," & _
"tblTemp.[EntryDate], tblTemp.[EntryPort]," & _
"tblTemp.[BrokerInvoiceNumber], tblTemp.[ConsigneeNumber]," & _
"tblTemp.[ImporterNumber], tblTemp.[ExportCountry]," & _
"tblTemp.[OriginCountry], tblTemp.[AirbillNumber]," & _
"tblTemp.[ManufacturerID], tblTemp.[LineItem]," & _
"tblTemp.[SpecialProgramCode], tblTemp.[TariffNumber]," & _
"tblTemp.[LineValueAmount], tblTemp.[DutyAmount]," & _
"tblTemp.[MPFAmount], tblTemp.[Brokerage]," & _
"tblTemp.[OtherCharges], tblTemp.[Period]," & _
"tblTemp.[EntryOnFile[B][COLOR=red]] "[/COLOR][/B] & _
"FROM tblTemp " & _
"ORDER BY tblTemp.[RandomNumber];"

[/SIZE][/FONT]

 

[tho28199]

Added the spaces as suggested, but still getting the same error.

 

[boblarson]

the only other thing I can think of (and someone else might see something else that I missed) is that you have created a procedure with the same name as one of the tables, fields, or variables.

 

[tho28199]

Problem solved... the culprit was the variable varSampleSize, which was being set to null because the user did not follow the process through to completion initially, so the variable value was reset when they 'stepped' back into the process at a later step.