On a form there are two date/time f

[Conner]

On a form there are two date/time fields. The first field is sign-in and the second is sign-out. Both fields use the Now()function to get the user sign-in and sign-out times. Obviously, the form gets used at the beginning of the workday and the end of the workday.

Sometimes a user gets confused, etc. and selects the wrong command or forgets to sign-in, but will sign-out.I need someway to "find" those occasions....I'm trying to build a query to check for these exceptions.

But when I run a query with [SignIn] and [SignOut], I sometimes get something like this:

[SignIn] [SignOut]
Jones 1/21/2004 8:30 A.M. 'nothing here
Smith 'nothing here 1/21/2004 5:00 p.m.

And then if I try to use any date/time functions, the empty spaces get filled with #Error.

What's in the blank spaces (remember I'm using Now() to get my date/Time)

Is there away to correct this?

 

[jrbarnett]

You can test for null in the fieldname if nothing has been entered (enter Is Null in the query criteria) and also can check for sign in time being > sign out time, which would pick up items put in out of order.

John

 

[GingerR]

for the future, to alleviate the "forgetful" users...you could instead put code in the Form's OnOpen and OnClose events which put the date/time stamp in there automatically. no more nulls.

 

[Conner]

Jr-
I tried testing for null (IsNull) in the query criteria. I get the error message "Data Type Mismatch in criteria expression." Didn't work. The "greater than" approach didn't work either. What is in an empty date/time field that doesn't get populated by the Now() as it should?

I like Ginger's suggestion, but alas, I lived too long in the present when I should have been thinking about the future of Now()

Suggestions?

 

[GingerR]

how many blanks are there? maybe just going thru and populating them manually with the same as the (non-missing) In or Out will be sufficient for now? Then you can put the code in the form's OnOpen and OnClose events (shouldn't take you but a few minutes).