access sql syntax w/ coldfusion

I've been looking at this error for days and can't seem to find anything wrong to return this error, if I enter the data manually and fill out all the fields and put it in Access, it works, but then using CF to pass this returns an error below......

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

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

SQL = "INSERT INTO tblfachecklist(studentid, faa, spcs, uda, rs, fdlc, eccf, ec, xc, mailoutdate, dpl, dl, ssac, ac, ofafsa, iir, anr, isir, ssc, insv, ts, vw, sbstu, sbpar, sbspo, tax, spn, plus, ccd, lc, rc, nt, add1, add2, add3) VALUES(1649, 1, 1, 0, 0, 0, 0, 0, 0, , 0, 0, 0, 0, , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, , 0, 0, 0, 0, , , , , )"


the query
<cfquery datasource=#aihs# name="newfachecklist">

INSERT INTO tblfachecklist(studentid, faa, spcs, uda, rs, fdlc, eccf, ec, xc, mailoutdate, dpl, dl, ssac, ac, ofafsa, iir, anr, isir, ssc, insv, ts, vw, sbstu, sbpar, sbspo, tax, spn, plus, ccd, lc, rc, nt, add1, add2, add3) VALUES(#studentid#, #faa#, #spcs#, #uda#, #rs#, #fdlc#, #eccf#, #ec#, #xc#, #mailoutdate#, #dpl#, #dl#, #ssac#, #ac#, #ofafsa#, #iir#, #anr#, #isir#, #ssc#, #insv#, #ts#, #vw#, #sbstu#, #sbpar#, #sbspo#, #tax#, #spn#, #plus#, #ccd#, #lc#, #rc#, #nt#, #add1#, #add2#, #add3#)

</cfquery>

all the fields are type YES/NO except these

studentid - number
mailoutdate - date/time
ofafsa - text
tax - text
rc - text
nt - date/time
add1 - text
add2 - text
add3 - text

would appreciate any suggestions, thanks, I did put single quotes around the text items but that didn't seem to help

Here are two things that I would start with.
1. mailoutdate :: if the field in your access database is defined as a date/time field, then convert the input to odbc format --<cfset mydate = #createODBCdateTime(mailoutdate)#> the insert #mydate#

2. make sure that in your insert statement that all your text fields are surrounded by quotes, otherwise access looks at the data as numeric.

I use access as my back end quite abit and working with date fields always gives me a headache. when you output your date field you will have to use #dateformat(var,'mask')# to view the date in a easily readable format.

hope this helps

I also think you can't pass in those empty comma separated values at the end of your statement. You either have to pass in something or don't pass in anything and take the columns out of the query.

actually, those empty commas is the actual output of the query dump from CF because those variables are empty strings, the original sql I wrote just pass variables, what should I do to pass empty strings? I tried assigning &nbsp; to the string if it's empty but that didn't seem to get rid of the error .... thanks

have you tried using <cfparam> to set null values for those strings?

hmm, if i fill in something for the fields that are dates then the query gets executed ok and it inserts, so I guess I'll have to come up w/ some sort of default date?

go into access, in design view for the table click on the date field and on the general tab. make sure required is set to "NO"

I would do the same for the other fields that would normally be blank.

If you want it to pass empty strings you must use '#faa#' which if faa was an empty string would end up being ''. Note that ALL of your string-based columns MUST have quotes around the values or the query will fail (this is not a CF thing it is a database thing).

I already did that to all of the fields in the db, I just looked over my other project I did long time ago, I had to put a default date in there so I think I'm just gonna put some fictitious date in there, unless there's a better way ...... thanks guys

Well the best way is to put quotes around string values and pass the correct columns.