Solved Syntax Error in Query Expression (1 Viewer)

[dccjr3927]

I have a query

strGetDailyRecsSQL = "SELECT LarqOrder, LarqSerial, Date_Time INTO tempExport FROM LarqData WHERE Date_Time >= #" & Format(dteLastExport, "mm-dd-yyyy") & "#;"

but am getting Syntax error in date in expression 'Date_Time >= #'.

I am sure I am just over looking something. Any help would be greatly appreciated.

 

[June7]

I don't see anything wrong with that SQL. Why use Format()?

See if enclosing Date_Time in [ ] makes a difference.

Suggest the table be permanent and instead of SELECT INTO, use INSERT INTO SELECT. DELETE records first at beginning of procedure.

 

[smtazulislam]

Can you check it

strGetDailyRecsSQL = "SELECT LarqOrder, LarqSerial, Date_Time INTO tempExport FROM LarqData WHERE Date_Time >= #" & Format(dteLastExport, "mm-dd-yyyy" OR dd-mm-yyyy) & "#; "

:

 

[isladogs]

In SQL statements, dates need to be in mm/dd/yyyy or yyyy-mm-dd format.
Try changing your code to

strGetDailyRecsSQL = "SELECT LarqOrder, LarqSerial, Date_Time INTO tempExport FROM LarqData WHERE Date_Time >= #" & Format(dteLastExport, "mm/dd/yyyy") & "#;"

Suggest you rename the Date_Time field

 

[June7]

Hyphen works as well as slash.

 

[dccjr3927]

Something like this?

strGetDailyRecsSQL = "INSERT INTO tempExport (LarqOrder, LarqSerial) SELECT LarqOrder, LarqSerial FROM LarqData WHERE [Date_Time] >= #" & dteLastExport & "#;"

Not really sure on the syntax for this, and a quick Google search yielded examples that were...less than clear.

I am still getting the same syntax error on the expression '[Date_Time] >= #'.

 

[Isaac]

Something like this?

strGetDailyRecsSQL = "INSERT INTO tempExport (LarqOrder, LarqSerial) SELECT LarqOrder, LarqSerial FROM LarqData WHERE [Date_Time] >= #" & dteLastExport & "#;"

Not really sure on the syntax for this, and a quick Google search yielded examples that were...less than clear.

I am still getting the same syntax error on the expression '[Date_Time] >= #'.

Did you see IslaDog's post?

 

[dccjr3927]

Yes. Date_Time is used probably 30-40 other times in this database. I cannot see where the field name would cause a syntax error. Likewise, all of the dates in this database are formatted mm-dd-yyyy. Neither has ever caused any issues. I discovered that in order to use the TransferText function, it has to reference an object, and not just an SQL statement. Hence why I changed to June7's recommended INSERT INTO SELECT code.

No matter whose code I use, it still renders the same syntax error.

 

[June7]

Want to provide db?

 

[dccjr3927]

Here is the database. This copy only has the module with the code and the two test tables tables.

 

[June7]

Need to provide local tables in db.

 

[Galaxiom]

The error is because there is nothing in dteLastExport. You have misunderstood this throughout the code module.
VBA variables are not included in the query scope.

Use a DMax or read the first record of a recordset based on the query. You must explicitly get that value to the variable.

dteLastExport = DMax("LastExport", "ExpTracking" )

Also note that Execute does not work on Select queries at all.

 

[dccjr3927]

Thank you Galaxiom. That did the trick.

June7 - Apologies for the linked tables. I did forget to convert them.

The good news is Problem Solved. Thanks to everyone for their input and suggestions.