compare date in vba

[dai_lo]

Hi,

I would like to compare an input date and current date in a IF statement

I have no idea why the following code doesn't work properly

If Format(Itm_Date_of_Shipment, "mm/dd/yyyy") >= format(now(), "mm/dd/yyyy") then

' update the table...

else

' display error message

end if

please advise.

thanks

ALice

 

[chergh]

try:

If clng(Itm_Date_of_Shipment) >= clng(date())

 

[Moniker]

Also, look into the DateDiff function. (Look in Access Help.) It does all the math for you.

 

[dai_lo]

DateDiff function does the work!

thanks

 

[RoyVidar]

The reason why the original statement didn't work as expected, is that through the Format statement, you converted two valid dates to strings, then you compared those two strings (mm/dd/yyyy) -> "12/01/2007" > "01/11/2008"

If the Itm_Date_of_Shipment is a variable of datatype Date/Time, then there's nothing wrong with doing date comparisons.

If Itm_Date_of_Shipment >= Date Then

Note usage of Date (gives todays date) vs Now (gives current Date and Time)