decision based on listbox value 1

[Apollo13a]

I'm trying to write code to compare the values from two list boxes but I get an invalid use of null error. Here's my set up. I have tblSignIn with fields of EmpID(PK), SignInDate, TicketNo, StatusID,StartDate,TermDate. I have created two queries, qryStartDate finds the latest StartDate(I used the Max function on the field StartDate) and qrySignIn finds the latest Sign In Date(Again I used the Max function on the SignInDate field). Then I created two list boxes and populated these with the respective query. When I run my form the list boxes show the correct dates. In the on current event of my form I qrote:
dim StartDate as date
Dim SignInDate as date
StartDate=me.lstStartDate
SignInDate = me.lstSignInDate
When I run my form, I get the error,"invalid use of null".
I tried using the column count but it didn't help. When I place my mouse cursor over the first variable, it says 12:00 AM. ??? Basically I'm trying to compare these two dates to see which is the latest and then display a message on my form. Also, is there a way to populate a textbox based on these queries?
Thanks Jim

 

[JR2913]

OK Jim

I have created the scenario you paint but changed the listboxes to text boxes

txtSigninDate has the data control source set to =DMax("[SigninDate]","tblSignin&quot

txtStartDate has the data control source set to =DMax("[StartDate]","tblSignin&quot

A third text box has been created with the On-Open event of the form set as

Private Sub Form_Open(Cancel As Integer)

If Me.txtSignInDate > Me.txtStartDate Then
txtCompareDates.Value = "Sign In Date is greater than Start Date"

Else

txtCompareDates.Value = "Start date is later"

End If

End Sub



Seems to work for me and satisfies what you stated you were trying to produce - but whether this is enough depends what else you want to do with the data.


Regards

John R

 

[Apollo13a]

Thanks John, I'm going to try it now. Do you think this table design is correct to provide a 'Last SignIn Date' and a 'First SignIn Date from just the data 'SignIn Date'(which is the only data that will be entered) Basically I'm trying to track when employees sign an out of work book. Currently I do this by hand but would like to speed it up with Access. My problem is that an employee will sign the book every Mon. until they get an assignment. They can work on the assignment any length of time, when they are done they sign the book again. I would like to track the when they First signIn for each time period they sign. My current query always goes back to the very first time they signed. I know I need to add some kind of IDnumber or a second table to track the sign in event periods or possibly a second relationship?
Thanks again,
Jim