INSERT INTO with a date field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to run an SQL in VB that will append the contents of a query into
a separate, linked table. Everything is going swimmingly except for a date
field (here named 'update'). Both the original source table (accessed from
the query) and the destination table are defined as date/time. I get this
error: run-time error '3134': Syntax error in Insert Into statement.

My sql is:

strSQLAppend = "INSERT INTO Plots " _
& "([Plot Code], Update, User, [Species counter]) " _
& "SELECT PlotCode, Update, User, SpeciesCounter " _
& "FROM qryNPSPlots_Plots;"

I've currently got 'Update' in the source query defined as:

Update: Format([ObsPoints]![DateTimeLastModified],"Short Date")

the SQL runs just fine when the date field 'Update' is removed.

Can I define the date field more explicitly in the SQL? what's missing?

Thanks in advance.
 
UPDATE is a reserved word in SQL.

Try placing the name in square brackets, and modify with the table name,
i.e.:
strSQLAppend = "INSERT INTO Plots " _
& "([Plot Code], [Update], [User], [Species counter]) " _
& "SELECT PlotCode, qryNPSPlots_Plots.[Update], qryNPSPlots_Plots.[User],
SpeciesCounter " _
& "FROM qryNPSPlots_Plots;"

See if JET will let you get away with that.
 
Yep, that did the trick! Thank you very much. (how embarassing I missed it...)

tim

Allen Browne said:
UPDATE is a reserved word in SQL.

Try placing the name in square brackets, and modify with the table name,
i.e.:
strSQLAppend = "INSERT INTO Plots " _
& "([Plot Code], [Update], [User], [Species counter]) " _
& "SELECT PlotCode, qryNPSPlots_Plots.[Update], qryNPSPlots_Plots.[User],
SpeciesCounter " _
& "FROM qryNPSPlots_Plots;"

See if JET will let you get away with that.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tim Howard said:
I'm trying to run an SQL in VB that will append the contents of a query
into
a separate, linked table. Everything is going swimmingly except for a date
field (here named 'update'). Both the original source table (accessed from
the query) and the destination table are defined as date/time. I get this
error: run-time error '3134': Syntax error in Insert Into statement.

My sql is:

strSQLAppend = "INSERT INTO Plots " _
& "([Plot Code], Update, User, [Species counter]) " _
& "SELECT PlotCode, Update, User, SpeciesCounter " _
& "FROM qryNPSPlots_Plots;"

I've currently got 'Update' in the source query defined as:

Update: Format([ObsPoints]![DateTimeLastModified],"Short Date")

the SQL runs just fine when the date field 'Update' is removed.

Can I define the date field more explicitly in the SQL? what's missing?

Thanks in advance.
Click to expand...
Click to expand...
 
Back
Top