Results 1 to 4 of 4
  1. #1
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295

    How to deal with empty date/time data type fields

    Good Afternoon All,




    I am trying to reference a field in a table of data type Date/Time. However if the field contains empty values an error message is raised indicating that "datatype mismatched in criteria expression"


    can anyone guide me how to get around this problem as I would have some empty fields

    Here's the code I am using:


    Private Sub Command17_Click()

    DoCmd.OpenForm "Edit Signin", acNormal, , "OfficerName = '" & WorkersName & "' And Datevalue(tblSignin.DateCreated) =# " & DateValue(Date) & "#"

    End Sub

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You can test for a Null value with IsNull(tblSignin.DateCreated).

    For example, if you only want to open the form if the field is not Null, you can use:

    Code:
    Private Sub Command17_Click()
    
     if not isnull(tblSignin.DateCreated) then
      DoCmd.OpenForm "Edit Signin", acNormal, , "OfficerName = '" & WorkersName & "' And Datevalue(tblSignin.DateCreated) =# " & DateValue(Date) & "#"
    endif
    
     End Sub

  3. #3
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295
    Thanks for your response John_G


    Please not that I want a set of records returned but only the ones that have a value in tblsignin.DateCreated field of the table. Hence the test has to be for all the records in the table.

    So as expected I am getting an error message saying: "Object Required"

    Also note that I did not explicitly open the table; I simple open a form that has a table as its data source.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Please not that I want a set of records returned but only the ones that have a value in tblsignin.DateCreated field of the table.
    Do you want to see all records which have a value (any value) in tblsignin.DateCreated, or do you want only those with a specific value?

    Right now your Openform will show records for one worker (Workersname) and with tblsignin.DateCreated = the current date.

    What line of code is being executed when you get that error message?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 01-25-2016, 01:36 PM
  2. Replies: 11
    Last Post: 07-20-2014, 06:22 PM
  3. Replies: 2
    Last Post: 04-23-2012, 10:13 PM
  4. Replies: 1
    Last Post: 08-07-2011, 07:58 AM
  5. Replies: 1
    Last Post: 07-14-2008, 12:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums