I'm getting a syntax error in Access 5

[Sherylj]

I'm getting a syntax error with this INSERT statement. Does anything jump out at anyone? I can't find anything wrong. Could it be a date or time format problem? All fields match. Would an UPDATE QUERY be required if I'm AUTONumbering the ID field and not passing it from the form?

I have this exact code working with another form but the fields and datasource are different. It also has the Date and Time as one field instead of separated into two. I'm working with existing data so I can't change date and time to one field (which would be my preference).
Thanks.
Sherylj

<CFSET time = #CreateODBCTime(Now())#>

<CFQUERY NAME=&quot;AddWellData&quot; DATASOURCE=&quot;sdwreport&quot; dbtype=&quot;ODBC&quot;>
INSERT INTO emailReportingform (Date, Time, Yard, wellname, Operator, Appearance, Road, Spills, Stairs, Hatches, Rock_Boxes, Leaks, Dlpump, PDpump1, Oiler, Belts, PDpump2, Well, Well_lines, Head, Shutoffs, Pops, DateSold, Temp, Gravity, Grossbbl, BS_W, Ticket, Totalbbl, pTk1, pTk2, pTk3, pTotalbbl, ohTk1, ohTk2, ohTk3, ohTotalbbl, swoTk1, swoTk2, swoTk3, swoTk4, swoTk5, swoTk6, swoTk7, swoTk8, swoTk9, swoTk10, swoTk11, swoTk12, swoTotalbbl, GBTk1, swtTk2, swtTk3, swtTk4, swtTk5, swtTk6, swtTk7, swtTk8, swtTk9, swtTk10, swtTk11, swtTk12, swtTotalbbl, BBLpumpedYD, TubingPsi, BBLpumpTD, annularPsi, TBBlmonth, pitskimed, Comments)
VALUES ('#DateFormat(Now(),&quot;mm dd, yy&quot#', '#time#', '#Form.Yard#', '#Form.wellname#', '#Form.Operator#', '#Form.Appearance#', '#Form.Road#', '#Form.Spills#', '#Form.Stairs#', '#Form.Hatches#', '#Form.Rock_Boxes#', '#Form.Leaks#', '#Form.Dlpump#', '#Form.PDpump1#', '#Form.Oiler#', '#Form.Belts#', '#Form.PDpump2#', '#Form.Well#', '#Form.Well_lines#', '#Form.Head#', '#Form.Shutoffs#', '#Form.Pops#', '#Form.DateSold#', '#Form.Temp#', '#Form.Gravity#', '#Form.Grossbbl#', '#Form.BS_W#', '#Form.Ticket#', '#Form.Totalbbl#', '#Form.pTk1#', '#Form.pTk2#', '#Form.pTk3#', '#Form.pTotalbbl#', '#Form.ohTk1#', '#Form.ohTk2#', '#Form.ohTk3#', '#Form.ohTotalbbl#', '#Form.swoTk1#', '#Form.swoTk2#', '#Form.swoTk3#', '#Form.swoTk4#', '#Form.swoTk5#', '#Form.swoTk6#', '#Form.swoTk7#', '#Form.swoTk8#', '#Form.swoTk9#', '#Form.swoTk10#', '#Form.swoTk11#', '#Form.swoTk12#', '#Form.swtTotalbbl#', '#Form.BBLpumpedYD#', '#Form.TubingPsi#', '#Form.BBLpumpTD#', '#Form.annularPsi#', '#Form.TBBlmonth#', '#Form.pitskimed#', '#Form.Comments#')
</CFQUERY>

Error Message is.....

ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.


Data Source = &quot;sdwreport&quot;


The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (6:1) to (6:65) in the template file c:\inetpub\

http://wwwroot\sdwreport\details.cfm.

 

[calista]

Try it without the single quotes around #time#. I have found that when you are using and OBDC date/time object, you do not need the quotes. Calista :-X
Jedi Knight,
Champion of the Force

 

[CFHub]

Yes, &quot;Date/Time&quot; objects will not require the &quot;text delimiters (')&quot;

You may want to use something other than &quot;Time&quot; as a field though becasue that is a reserved word....

http://cfhub.com/faq/date_inserts.cfm

 

[GunJack]

Hi Sheryl,

In addition, you'll need to format your dates in the manner Access expects them. The dateformat function is used to display dates in a human readable fashion. The createOdbcDateTime function is for formatting dates to be passed into sql statements.

Combining this with Calista's statment about the single quotes, you'll want to change

('#DateFormat(Now(),&quot;mm dd, yy&quot#',

to

(#createOdbcDateTime(Now()# , .... remaining values ...

assuming your field is a dateTime field and not a text field.

Along the lines of what CFHub said, it's generally not a good idea to use words such as &quot;date&quot;, &quot;time&quot;, &quot;form&quot;, etc... as variable and field names as they are usually reserved words in CF, Access, Sql, etc.. You can get around this in Access by surrounding field names with double quotes but it's generally not a good idea.

Insert into table1 (&quot;date&quot;,&quot;time&quot values (...)

Hope this helps,
GJ

 

[Sherylj]

The problem I'm having is they are 2 separate fields. One is Date that will be the current date and the other is Time which will be the current time of day. Unfortunately, this is an existing database (not designed very well) that separates the 2 fields. I've done the #DateFormat(Now(),&quot;mmmm d, yyyy&quot# before. Just not separating the date and time.

I've changed my variables accordingly to todays_time and todays_date per your advice.

I'm still having no luck. Maybe it's something else. I have quite a few fields that must be numbers but it's being checked. Thanks! If you have other ideas, let me know.

Sheryl j

 

[GunJack]

Hey Sheryl,

I would next ask what database are you using and what are the field types of your date & time fields?

If I have an insert problem like this where I can't see anything wrong, I start by removing half of the fields and see if it inserts. If so, I paste half of them back in and keep repeating this until I find the guilty field. At that point, it usually doesn't take long to find the problem.

If you can narrow it down to the exact field, that will help us diagnose it as we're just assuming it's a date field at the moment. In a long query such as yours, it could easily be a missing comma or other difficult to spot syntax error.

GJ

 

[Sherylj]

I'm doing just that right now. I didn't get past the first field! The Date field of course. Been in a meeting and may have to pick this up tomorrow. I'll let you know.

Sherylj

 

[Sherylj]

Access 2000 and Text field. Not even a Date field. Does it have to be a date field? Maybe that's what's wrong. My other database (I designed) is Date/Time but this one came to me as a text field.

Sheryl

 

[Sherylj]

Got it! When I changed my field names to Current_Date and Current_Time had NO PROBLEM. Thanks for the info on reserved names!

I'm sure you'll be hearing from me soon! I'm doing major development right now.

Sherylj