dcsimg
www.webdeveloper.com
Results 1 to 9 of 9

Thread: Null date value

Hybrid View

  1. #1
    Join Date
    Jan 2003
    Location
    PA
    Posts
    1,898

    Null date value

    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!!

  2. #2
    Join Date
    Jan 2003
    Location
    USA
    Posts
    688
    How about if the date is blank (i.e. ""), change it to Null before going into the database.

  3. #3
    Join Date
    Jan 2003
    Location
    PA
    Posts
    1,898
    Sorry, but I don't quite understand. You mean instead of "ommitted"? I've tried using "Null" and I get the same thing.

  4. #4
    Join Date
    Jan 2003
    Location
    USA
    Posts
    688
    Don't try to put "" or any text into a date/time database field. But you can put a date in or a Null.

  5. #5
    Join Date
    Jan 2003
    Location
    PA
    Posts
    1,898
    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?

  6. #6
    Join Date
    Jan 2003
    Location
    USA
    Posts
    688
    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.

  7. #7
    Join Date
    Jan 2003
    Location
    PA
    Posts
    1,898
    It is set to No. I got it fixed. Thanks

  8. #8
    Join Date
    Jun 2010
    Posts
    1

    how did you fix it?

    Hi Jrthor2,

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

    Thanks in advance
    Aaron

  9. #9
    Join Date
    Jan 2008
    Location
    Florida
    Posts
    1,227
    You can always use IsDate to validate a date value

    If IsDate(myvalue) then ....

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles