How to compare dates in an IF statement

[Amit]

MS Access 2K, Windows XP
====================
Hi,

I have a form with a text control txtDate and Control Source set to Date().
I also have a list with date in one of the columns.

I'm trying to compare these two dates in an IF statement in OnClick event of
a button on the form, and am getting opposite results than expected.

The value of date in the List is 9/13/2005 and the value of date for txtDate
is 9/14/2005.

When I do the following comparison:
If (Me.listCurrentUsers.Column(4, varItem) < Me.txtDate)
I get "False". When I use ">" instead, I get "True".
(varItem is for the selected row in the list)

I've checked the values of these controls using MsgBox, and the dates are
displayed correctly.

Am I missing something obvious here? Shouldn't my IF statement compute to
"True" since 9/13/2005 is less than 9/14/2005.

I did try enclosing the controls in "#" but that gave me compile error.

Would appreciate some help on this, as I have a feeling I'll be kicking
myself after someone points me in the right direction...

Thanks.

-Amit

 

[Wayne Morgan]

If (Me.listCurrentUsers.Column(4, varItem) < Me.txtDate)

If Me.txtDate is =Date(), then you could use Date in the If statement
instead of referring to the textbox. However, that isn't the problem. The
values in the textbox and listbox column are being treated as text, not as
dates. Try using date delimiters around them to specify that they are dates.

If ("#" & Me.listCurrentUsers.Column(4, varItem) & "#" < "#" & Me.txtDate &
"#")
or
If ("#" & Me.listCurrentUsers.Column(4, varItem) & "#" < Date)

Be aware, that if one of the "dates" includes time, it may through off your
equation, depending on what you're asking for. Using only "<", you probably
won't have a problem because the time associated with the date in txtDate
will be midnight.

 

[Amit]

If Me.txtDate is =Date(), then you could use Date in the If statement
instead of referring to the textbox. However, that isn't the problem. The
values in the textbox and listbox column are being treated as text, not as
dates. Try using date delimiters around them to specify that they are dates.

If ("#" & Me.listCurrentUsers.Column(4, varItem) & "#" < "#" & Me.txtDate &
"#")
or
If ("#" & Me.listCurrentUsers.Column(4, varItem) & "#" < Date)

Hi Wayne,

Thanks for the response. Yes, I tried your suggestion and it worked! I'd
also figured out another solution after doing some more searching on the net
- using CDate.

"If (CDate((Me.listCurrentUsers.Column(4, varItem))) < Me.txtDate.Value)"
worked too.

Thanks!!

-Amit