Insert STMT Error

[Anthony Acri]

I am trying to execute the following Insert Stmt in some VBA code

nsertSQL = "INSERT INTO [Borgo Production Order Temporary Master Table] "
InsertSQL = InsertSQL + "( [Production Order Number], [Customer Number],"
InsertSQL = InsertSQL + " [Address Field 1], [Address Field 2], [Address
Field 3],"
InsertSQL = InsertSQL + " [Address Field 4], [Address Field 5],"
InsertSQL = InsertSQL + " [Deliver To 1], [Deliver To 2],"
InsertSQL = InsertSQL + " [Deliver To 3], [Deliver To 4],"
InsertSQL = InsertSQL + " [Deliver To 5],"
InsertSQL = InsertSQL + " [Customer PO],"
InsertSQL = InsertSQL + " [Order Date] )"
InsertSQL = InsertSQL + ") VALUES "
InsertSQL = InsertSQL + "( '" & Order_No & "', '" & Cust_No & "',"
InsertSQL = InsertSQL + " '" & Cust_Name & "', '" & Cust_Address1 & "', '" &
Cust_Address2 & "', "
InsertSQL = InsertSQL + " '" & Cust_Address3 & "', '" & Cust_Zip_Code & "' ,
"
InsertSQL = InsertSQL + " '" & Ord_Ship_Name & "', '" & Ord_Ship_Address1 &
"' , "
InsertSQL = InsertSQL + " '" & Ord_Ship_Address2 & "', '" &
Ord_Ship_Address3 & "' , "
InsertSQL = InsertSQL + " '" & Ord_Ship_Zip & "' , "
InsertSQL = InsertSQL + " '" & Ord_Cust_Po_No & "' , "
InsertSQL = InsertSQL + " '" & Ord_Date & "' ) "

The database field 'Order Date' is defined as a Date

My variable field Ord_Date is defined as a Date

After trying to execute I get a run-time error 3134:

Syntax error in INSERT INTO stmt

When I tried to insert the date field I get this error.

I biuld my variable field as follows:

convDate = Left(Orders1Recs("ORD_ORDER_DATE"), 4) + "/" + _
Mid(Orders1Recs("ORD_ORDER_DATE"), 5, 2) + "/" + _
Right(Orders1Recs("ORD_ORDER_DATE"), 2)
Ord_Date = CDate(convDate)

In the insert stmt do I need to put something loke "#" near the date field?

Any help is appreciated

 

[Tony Toews]

InsertSQL = InsertSQL + " '" & Ord_Date & "' ) "

Change to
InsertSQL = InsertSQL + " #'" & Ord_Date & "# ) "

Currently Access thinks you are giving it a text field. # are the
date delimiters similar to ' being text field delimiters.

I biuld my variable field as follows:

convDate = Left(Orders1Recs("ORD_ORDER_DATE"), 4) + "/" + _
Mid(Orders1Recs("ORD_ORDER_DATE"), 5, 2) + "/" + _
Right(Orders1Recs("ORD_ORDER_DATE"), 2)
Ord_Date = CDate(convDate)

This assumes that the date field coming in is a text field in yyyymmdd
format?

Finally SQL statements require that the dates be in mm/dd/yy, or
mm/dd/yyyy format. You can't assume that the system you are working
on is using those date formats. Thus you should use the logic at the
following web page.
Return Dates in US #mm/dd/yyyy# format
http://www.mvps.org/access/datetime/date0005.htm

In the insert stmt do I need to put something loke "#" near the date field?

Correct.
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm