SQL Insert Into Giving Me Error

[Johnny Derk]

I am trying to insert a record into an access database using a flash form on
the entry page and an insert into clause on the action page. I continue to get
an invalid insert into error. Here is my SQL code: Underneath the code I have
the cfoutput of the form fields on to a test page.

<cfquery datasource="Users">
INSERT INTO Users_1 (UserName, Password, Email, Phone, City, State,
Address, Zip, Building_Num, PriceHi, PriceLo, Bed, Bath, Acreage, City_Search,
PropertyType, View, Frontage)
VALUES (
<cfif IsDefined("FORM.Username") AND #FORM.Username# NEQ
"">'#Form.Username#'<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.Password") AND #FORM.Password# NEQ
"">'#Form.Password#'<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.Email") AND #FORM.Email# NEQ
"">'#Form.Email#'<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.Phone") AND #FORM.Phone# NEQ
"">'#Form.Phone#'<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.City") AND #FORM.City# NEQ
"">'#Form.City#'<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.State") AND #FORM.State# NEQ
"">'#Form.State#'<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.Address") AND #FORM.Address# NEQ
"">'#Form.Address#'<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.Zip") AND #FORM.Zip# NEQ
"">#Form.Zip#<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.Building_Num") AND #FORM.Building_Num# NEQ
"">'#Form.Building_Num#'<cfelse>, NULL</cfif>,
<cfif IsDefined("FORM.PriceHi") AND #FORM.PriceHi# NEQ
"">#Form.PriceHi#<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.PriceLo") AND #FORM.PriceLo# NEQ
"">#Form.PriceLo#<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.Bed") AND #FORM.Bed# NEQ
"">#Form.Bed#<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.Bath") AND #FORM.Bath# NEQ
"">#Form.Bath#<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.Acreage") AND #FORM.Acreage# NEQ
"">#Form.Acreage#<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.City_Search") AND #FORM.City_Search# NEQ
"">'#Form.City_Search#'<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.PropertyType") AND #FORM.PropertyType# NEQ
"">'#Form.PropertyType#'<Cfelse>NULL</cfif>,
<cfif IsDefined("FORM.View") AND #FORM.View# NEQ
"">'#Form.View#'<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.Frontage") AND #FORM.Frontage# NEQ
"">'#Form.Frontage#'<cfelse>NULL</cfif>)
</cfquery>

Cfoutput of same code on test page is:
'Username', NULL, 'Email', 'Phone', 'City', 'ST', 'Address', Zip, 'Bldg',
999999999, 0, 0, 0, NULL, NULL, NULL, NULL, NULL

I don't see why this is not a valid insert clause. Any help would be much
appreciated.Text

[jdeline]

You need a closing ) after your last </CFIF>

[Johnny Derk]

I have it in my code. Somehow that ")" turned into that smily face at the end
of my coded when I submitted it. User error I geuss. Thank You for your reply,
If you have any other suggestions please let me know.

[Dan Bracuk]

Suggestion number 1, not related to your problem, is to make zip code a
character field rather than numeric. Otherwise you will have issues with
leading zeros 10 character zip codes.

To solve your problem, comment out fields 2 through 17 and attempt to insert a
record with just a username. Add one field at a time until it crashes, then
you'll know where your problem is.

[Johnny Derk]

Dan,

Thank you. Took Your advise, Found that the field names were conflicting with
the access reserved words. Just need to be more careful with my naming
convention. Also, Thank You for the tip on the Zip field. I will make that
change.