Comparing a Month & Day Combination

[Guest]

Hello Fellow Access User!

In my data, I have two fields that contain dates, say Field1 and Field2. I
have a query that creates another date field, say Field3, so that if the
combination of Month&Day in Field 1 (say 51 for 5/1/2005) is greater than the
combination of Month&Day in Field2, Field3 would return the year of Field 1,
otherwise it would return the year of Field2.

My query works fine except for cases when the month in Field1 or Field2 is
October or later, and the month in the other field is September or earlier.
For example, if the date in Field1 is 5/1/05 and the date in Field2 is
11/5/06, access compares 51 to 115, and for some reason returns 2005 instead
of 2006.

Any help with overcoming this issue would be greatly appreciated.

Thank you!

Magnivy

 

[John Vinson]

Hello Fellow Access User!

In my data, I have two fields that contain dates, say Field1 and Field2. I
have a query that creates another date field, say Field3, so that if the
combination of Month&Day in Field 1 (say 51 for 5/1/2005) is greater than the
combination of Month&Day in Field2, Field3 would return the year of Field 1,
otherwise it would return the year of Field2.

My query works fine except for cases when the month in Field1 or Field2 is
October or later, and the month in the other field is September or earlier.
For example, if the date in Field1 is 5/1/05 and the date in Field2 is
11/5/06, access compares 51 to 115, and for some reason returns 2005 instead
of 2006.

Any help with overcoming this issue would be greatly appreciated.

Thank you!

Magnivy

Well... don't *do* it that way then!

Rather than the ambiguous concatenation of two integers (is 113
January 13 or November 3?) use the Format() function:

Format([field1], "mmdd")

This will give an unambiguous text string 0113 or 1103.

Or, better perhaps, use actual dates: DateSerial(2000,
Month([field1]), Day([field1])) will return a date value (in the year
2000, which could be any year; this is just to remove any differences
in year). This date value will sort chronologically (and yes, 2000 was
a leap year so February 29 will work).

John W. Vinson[MVP]

 

[Guest]

Thanks John!

Hello Fellow Access User!

In my data, I have two fields that contain dates, say Field1 and Field2. I
have a query that creates another date field, say Field3, so that if the
combination of Month&Day in Field 1 (say 51 for 5/1/2005) is greater than the
combination of Month&Day in Field2, Field3 would return the year of Field 1,
otherwise it would return the year of Field2.

My query works fine except for cases when the month in Field1 or Field2 is
October or later, and the month in the other field is September or earlier.
For example, if the date in Field1 is 5/1/05 and the date in Field2 is
11/5/06, access compares 51 to 115, and for some reason returns 2005 instead
of 2006.

Any help with overcoming this issue would be greatly appreciated.

Thank you!

Magnivy

Well... don't *do* it that way then!

Rather than the ambiguous concatenation of two integers (is 113
January 13 or November 3?) use the Format() function:

Format([field1], "mmdd")

This will give an unambiguous text string 0113 or 1103.

Or, better perhaps, use actual dates: DateSerial(2000,
Month([field1]), Day([field1])) will return a date value (in the year
2000, which could be any year; this is just to remove any differences
in year). This date value will sort chronologically (and yes, 2000 was
a leap year so February 29 will work).

John W. Vinson[MVP]