data mismatch with Access 1

[OccasionalCoder]

Using cold fusion to insert data into an access table. When the date fields have dates in them, it works ok. When the date fields are blank, I get a data mismatch error.

There are instances when the date field has to be empty.

How do I adjust for this.

 

[TruthInSatire]

insert NULL

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[OccasionalCoder]

This is probably pretty basic stuff, but I can't find any documentation on it in my Access book, or in Cold Fusion documentation. My guess this is pretty much an access issue rather than Cold Fusion, so I appreciate your patience.

Here's the query I used first:


<cfquery datasource="#db#" username="#un#" password="#pw#">
insert into fellows(first_name, mi, last_name, photo, agency, abbr, division, school, program, synopsis, website, email, start_year, end_year, status)
values('#trim(form.first_name)#', '#trim(form.mi)#', '#trim(form.last_name)#', '#trim(form.photo)#', '#trim(form.agency)#', '#trim(form.abbr)#', '#trim(form.division)#', '#form.school#', '#trim(form.program)#', '#form.synopsis#', '#trim(form.website)#', '#trim(form.email)#', '#trim(form.start_date)#','#trim(form.end_date)#', '#form.status#')
</cfquery>

Then I adjusted the date/time like this:

...
<cfif isDefined("form.start_date")>
'#trim(form.start_date)#',
<cfelse>
'null',
</cfif>

etc.

In both instances I get the data type mismatch error. The only time it works is when I have a date typed in the form.
What am I doing wrong?

Thanks in advance for your help.

 

[ecobb]

Remove the quotes from around NULL



Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[OccasionalCoder]

I just tried that, and still get the same error message.

Thanks

 

[TruthInSatire]

<cfif isDefined("form.start_date")>
'#trim(form.start_date)#',
<cfelse>
'null',
</cfif>

most form fields will be present once you submit the page even if they don't contain any values. some exceptions are checkboxes, radio buttons, etc...

You need to check for empty values not a defined variable so change your cfif to
<cfif len(trim("form.start_date"))>

You don't need to check to see if it's defined so I left it out. Text fields will be, even with empty values.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[TruthInSatire]

oh, just to be clear you still need to remove the quotes from around the NULL as ecar said.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.