Calendar date format isn't correctly interpreted

Topic

New Reply

On the startup form of the attached mdb, the following code line associated with the button labelled “Report Preview”:
If Format(Forms!GioRep!CGior, “dd-mm-yyyy”) < Format(#1/12/2007#, "dd-mm-yyyy") Then
doesn't work correctly for certain dates; for example, if you select July 9, 2009, the condition evaluates to True.
How can I modify the line so as to work properly?

Reply | Quote

Replies

On the startup form of the attached mdb, the following code line associated with the button labelled “Report Preview”:
If Format(Forms!GioRep!CGior, “dd-mm-yyyy”) < Format(#1/12/2007#, "dd-mm-yyyy") Then
doesn't work correctly for certain dates; for example, if you select July 9, 2009, the condition evaluates to True.
How can I modify the line so as to work properly?

This converts dates to a text string, why dont you convert them to real dates then do the comparison.

Reply | Quote

This converts dates to a text string, why dont you convert them to real dates then do the comparison.

Thank you Pat

Reply | Quote

1. Literal dates in VBA use US date format m/d/yyyy, so #1/12/2007# is interpreted as January 12, 2007.

2. The result of Format is a string, so you are performing a string comparison, not a date comparison:

If “09-07-2009” < "12-01-2007" Then

Since 0 comes before 1, the condition is True.

There is no need to use Format. VBA is very well able to compare dates.

If your intention was to test whether CGior is before January 12, 2007, use

If Me!CGior < #1/12/2007# Then

and if you wanted to test whether it is before December 1, 2007, use

If Me!CGior < #12/1/2007# Then

I used Me instead of Forms!GioRep – if you ever decide to rename the form, Me will still work.

Reply | Quote

1. Literal dates in VBA use US date format m/d/yyyy, so #1/12/2007# is interpreted as January 12, 2007.

2. The result of Format is a string, so you are performing a string comparison, not a date comparison:

If “09-07-2009” < "12-01-2007" Then

Since 0 comes before 1, the condition is True.

There is no need to use Format. VBA is very well able to compare dates.

If your intention was to test whether CGior is before January 12, 2007, use

If Me!CGior < #1/12/2007# Then

and if you wanted to test whether it is before December 1, 2007, use

If Me!CGior < #12/1/2007# Then

I used Me instead of Forms!GioRep – if you ever decide to rename the form, Me will still work.

Thank you Hans

Reply | Quote