Null date value

[jrthor2]

I have an Access 200 database with a field set up as Date/Time and it is not required. When I go to insert something into the table, if there is not a date in that field, it gives me the error:

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

Here is the page code that inserts this info:
<%
'Set the response buffer to true as we maybe redirecting
Response.Buffer = True

Dim strNotes
strNotes = Replace(Request.Form("Notes"), "'", "''")
If Trim(Request.Form("MembershipDate")) = "" Then
strMembershipDate = "ommitted"
End If
If Trim(Request.Form("LastDateOfContact")) <> "" Then
strDateLastTalked = Replace(Request.Form("LastDateOfContact"), "'", "''")
strDateLastTalked = CDate(strDateLastTalked)
End If
If Trim(Request.Form("time_talents")) <> "" Then
strTalent = Replace(Request.Form("time_talent"), "'", "''")
End If

Set conn = server.createobject("adodb.connection")
DSNtemp="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("new_members.mdb")
conn.Open DSNtemp

SQLstmt = "INSERT INTO New_Members (LastName,FirstName,SpouseName,ChildrenNames1,Age1,ChildrenNames2,Age2,ChildrenNames3,Age3,ChildrenN ames4,Age4,Address,City,State,ZipCode,HomePhone,EmailAddress,MembershipDate,DateLastTalked,Notes,Pic tureID)"
SQLstmt = SQLstmt & " VALUES ('" & request.form("LastName") & "','" & request.form("FirstName") & "','" & request.form("SpouseName") & "','" & request.form("ChildrenNames1") & "','" & request.form("Age/Grade1") & "','" & request.form("ChildrenNames2") & "','" & request.form("Age/Grade2") & "','" & request.form("ChildrenNames3") & "','" & request.form("Age/Grade3") & "','" & request.form("Childrennames4") & "','" & request.form("Age/Grade4") & "','" & request.form("Address") & "','" & request.form("City") & "','" & request.form("State") & "','" & request.form("ZipCode") & "','" & request.form("PhoneNumber") & "','" & request.form("Email") & "','" & strMembershipDate & "','" & DATE_DELIMITER & strDateLastTalked & DATE_DELIMITER & "','" & strTalent & "," & strNotes & "','" & request.form("PictureID") & "')"
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>

<%

conn.execute(SQLstmt)

conn.Close
Set conn = nothing
Set SQLstmt = nothing
Response.Redirect("index.asp")
%>


All help is greatly appreciated!!

[Bullschmidt]

How about if the date is blank (i.e. ""), change it to Null before going into the database.

[jrthor2]

Sorry, but I don't quite understand. You mean instead of "ommitted"? I've tried using "Null" and I get the same thing.

[Bullschmidt]

Don't try to put "" or any text into a date/time database field. But you can put a date in or a Null.

[jrthor2]

I have tried this:

If Trim(Request.Form("MembershipDate")) = "" Then
strMembershipDate = Null
Else
strMembershipDate = Replace(Request.Form("MembershipDate"), "'", "''")
End If

and that gives me the same error, so how do I do what you are saying?

[Bullschmidt]

Then perhaps you might try opening up the database and for the field in question be sure that the Required property toward the bottom is set to No instead of Yes.

[jrthor2]

It is set to No. I got it fixed. Thanks

[asikora]

Hi Jrthor2,

How did you fix this? I'm dealing with the same issue , and it's driving me nuts

Thanks in advance
Aaron

[yamaharuss]

You can always use IsDate to validate a date value

If IsDate(myvalue) then ....