saving date as a null value

[Guest_imported]

I'm trying to update an access database via a web form. When I try to save it and a date field is blank I get a type missmatch error. I don't want a value in the date field. How do I force it, or (trick it) to take a Null or "" value?

 

[durug]

If the field allows null it shoud work. Type mismatch sounds to me like a different problem. Can you post the page?

 

[travmak]

I can't because I'm behind a serious fire wall (USMC Base).
but here is the code. if i change the NULL to a date i don't get the error.

function trap(stringToTrap)
if stringToTrap = &quot;&quot; or trim(len(trim(stringToTrap))) < 1 then
stringToTrap = NULL
end if
if stringToTrap <> null then
stringToTrap = REPLACE(stringToTrap, &quot;'&quot;, &quot;`&quot
end if
trap = stringToTrap
end function

 

[durug]

try instead of <> null to write is not null
the stringtotrap is the value that you need to insert?
Where is the error pointing to?
To the trap function, or to the update statement?
Can you show the update (insert statement)?

regards,
durug

 

[travmak]

I've come up with two solutions. (I think)
1. change the date field to a text field,
2. enter a NULL
3. change it back to a date field.

-or-

1. put in a ridiculous date like 1/1/1820
2. delete all field containing 1/1/1820

what do you think?

 

[travmak]

sqlupdate = &quot;UPDATE tbl_master_listing &quot; & _
&quot;SET tbl_master_listing.process = '&quot; & trap(request.Form(&quot;process&quot) & &quot;', &quot; & _
&quot;tbl_master_listing.subject = '&quot; & trap(request.Form(&quot;subject&quot) & &quot;', &quot; & _
&quot;tbl_master_listing.revision_number = '&quot; & trap2(request.form(&quot;revision&quot) & &quot;', &quot; & _
&quot;tbl_master_listing.custodian = '&quot; & trap(request.Form(&quot;custodian&quot) & &quot;', &quot; & _
&quot;tbl_master_listing.approving_authority = '&quot; & trap(request.Form(&quot;authority&quot) & &quot;', &quot; & _
&quot;tbl_master_listing.revision_date = '&quot; & trap(request.Form(&quot;revisiondate&quot) & &quot;', &quot; & _
&quot;tbl_master_listing.audit_date = '&quot; & trap(request.Form(&quot;auditdate&quot) & &quot;', &quot; & _
&quot;tbl_master_listing.stat = '&quot; & anal & &quot;' &quot; & _
&quot;WHERE tbl_master_listing.process = '&quot; & session(&quot;editwhat&quot & &quot;'&quot;

updatecon.Execute(sqlupdate)

--ERROR--

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/logistics_dev/masterupdate/savedata.asp, line 67

 

[durug]

there is NO problem inserting a null value in a date field.

there can be 2 option
-check to see if your field allows null
-try a small insert query in access where you try to insert a null value in the date field

If it's working then the problem is where you build your insert (update) statement

 

[travmak]

clip from microsoft access 97 help:

If you want to allow a field to be left blank and don't need to determine why the field is left blank, set both the Required and AllowZeroLength properties to No. This is the default when creating a new Text, Memo, or Hyperlink field.

a date field does not have the &quot;AllowZeroLength&quot; option in the properties option so you can't set it to no to allow the null value.

 

[durug]

Your problem here is your update statement look like this after being processed

update ... set ..audit_date = 'null'
you have the quotes outside the trap function and even if the trap function is returning the corect result you are trying to update a date field with a string value

if you use update ... set ...audit_date = null will work

 

[travmak]

THAT'S IT. I knew you were right with out having to look at my code again, I just went home instead. My solution:

(I used a &quot;learning&quot; site at home for the solution, I don't work saturdays. The names are different but same principle.)


function errortrap(stringToTrap)
stringToTrap = REPLACE(stringToTrap, &quot;'&quot;, &quot;`&quot
errortrap = stringToTrap
end function

function sqladd(sqlseg)
if isnull(sqlseg) or len(trim(sqlseg)) = 0 then
sqladd = &quot;NULL&quot;
else sqladd = &quot;'&quot; & errortrap(sqlseg) & &quot;'&quot;
end if
end function




sqlupdate = &quot;UPDATE info &quot; & _
&quot;set info.firstname = &quot; & sqladd(request.form(&quot;fname&quot) & &quot;, &quot; & _
&quot;info.lastname = &quot; & sqladd(request.Form(&quot;lname&quot) & &quot;, &quot; & _
&quot;info.shoesize = &quot; & sqladd(request.form(&quot;shoesize&quot) & &quot;, &quot; & _
&quot;info.eyecolor = &quot; & sqladd(request.Form(&quot;eyecolor&quot) & &quot;, &quot;& _
&quot;info.favoritecolor = &quot; & sqladd(request.Form(&quot;fcolor&quot) & &quot;, &quot; & _
&quot;info.favoritedrink = &quot; & sqladd(request.Form(&quot;fdrink&quot) & &quot;, &quot; & _
&quot;info.dob = &quot; & sqladd(request.Form(&quot;dob&quot) & &quot;, &quot; & _
&quot;info.favoritesong = &quot; & sqladd(request.Form(&quot;fsong&quot) & &quot;, &quot; & _
&quot;info.iq = '&quot; & trap2(request.Form(&quot;iq&quot) & &quot;' &quot; & _
&quot;WHERE info.username = '&quot; & session(&quot;editwho&quot & &quot;'&quot;