Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008
    Posts
    5

    HELP with NULL DateTime VALUES

    Hi,
    I'm creating a simple timeclock which I would like to have a running daily total of their hours during the day. For example, if they've clockin and then clock out for lunch it would give them their hours worked and then when they clocked out for the day it would give them the total hours for the day minus lunchtime.
    I have a module which calculates the time - it works perfectly if all punch fields have time in them. When there is a NULL time field it returns #ERROR in the field on teh form.
    My tables fields are clockin1, clockout1(lunch out), clockin2 (lunch in), and clockout2.
    here is my code:
    Dim interval As Double

    If IsNull(IN1) = True And IsNull(OUT1) = True And IsNull(IN2) = True And IsNull(OUT2) = True Then Exit Function

    If IsNull(IN1) = False And IsNull(OUT1) = False And IsNull(IN2) = True And IsNull(OUT2) = True Then
    interval = OUT1 - IN1
    ElseIf IsNull(IN1) = False And IsNull(OUT1) = False And IsNull(IN2) = False And IsNull(OUT2) = True Then
    interval = OUT1 - IN1
    ElseIf IsNull(IN1) = False And IsNull(OUT1) = False And IsNull(IN2) = False And IsNull(OUT2) = False Then
    interval = ((OUT2 - IN1) - (IN2 - OUT1))
    ElseIf IsNull(IN1) = False And IsNull(OUT1) = True And IsNull(IN2) = True And IsNull(OUT2) = True Then
    interval = OUT2 - IN1
    Else
    interval = 0


    End If

    When I step through the code - it doesn't seem to recognize a NULL datetime field. What am I doing wrong?
    Lisa

  2. #2
    Join Date
    Mar 2008
    Posts
    5
    I figured a way around my problem. I created text fields on my form and put the following in the control source property:
    IIf ([ClockIn1] is null, 0,[clockIN1])
    I did this for all 4 of my datetime fields and now my code works fine because I send the created text field to the function not the table field.

    I just have one more question - why was I having such trouble testing for null values on a datetime field?

  3. #3
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109
    I'm not sure if it was a typo, but I see a problem with your last condition. I believe the last check should be 'IsNull(OUT2) is FALSE' not TRUE. Otherwise, I have no idea why you had the problem.

    By the way, you may want to try another forum. That one gets more responses than this one does.

    http://www.access-programmers.co.uk/forums/

  4. #4
    Join Date
    Mar 2008
    Posts
    5
    I did catch that error at the end (should be false rather than true) but it was really strange that no matter what I did or how I did it - i couldn't get it to recognize a NULL date field. Very strange. I'm using my workaround rather than spend any more time on it. Just wondered if anyone had any ideas. thanks for the link and the reposonse.
    Lisa

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

Similar Threads

  1. Replies: 4
    Last Post: 08-19-2011, 01:53 PM
  2. Fill in Null values
    By Petefured in forum Queries
    Replies: 1
    Last Post: 10-06-2008, 12:54 PM
  3. Null vs New Record
    By jversiz in forum Access
    Replies: 2
    Last Post: 02-20-2008, 12:02 PM
  4. Replies: 0
    Last Post: 11-17-2006, 09:38 AM
  5. Null Values not showing up in a Query
    By Valli in forum Queries
    Replies: 0
    Last Post: 01-04-2006, 03:53 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