Date Comparison

[Guest]

I need to compare a date field with the current date and determine if it is
higher or lower, putting the answer in another field

 

[Guest]

Just a small tip - use the DateSerial function to make your comparisons in
code, especially if you are not using the US date format

 

[Guest]

UPDATE YourTable SET YourTable.[High-Low] =
IIf([YourDate]>Date(),"High","Low");

 

[John Vinson]

I need to compare a date field with the current date and determine if it is
higher or lower, putting the answer in another field

Well, you certainly should NOT store the result of this comparison in
another table field. If the comparison is True today, it might be
False tomorrow.

You can get a Yes/No field by a very simple expression: type

DateHasPassed: [Datefield] < Date()

in a vacant Field cell in a query. The expression will evaluate
(correctly, regardless of your computer's formatting) to either True
or False.

John W. Vinson[MVP]

 

[Guest]

John
I have tried this, but I am getting a problem with CHECK Contraints. How do
I check the date in one field against todays date and put the answer(YES/NO)
in another field.
Richard.

I need to compare a date field with the current date and determine if it is
higher or lower, putting the answer in another field

Well, you certainly should NOT store the result of this comparison in
another table field. If the comparison is True today, it might be
False tomorrow.

You can get a Yes/No field by a very simple expression: type

DateHasPassed: [Datefield] < Date()

in a vacant Field cell in a query. The expression will evaluate
(correctly, regardless of your computer's formatting) to either True
or False.

John W. Vinson[MVP]

 

[John Vinson]

John
I have tried this, but I am getting a problem with CHECK Contraints. How do
I check the date in one field against todays date and put the answer(YES/NO)
in another field.
Richard.

Do you want to store the Yes/No in another field IN YOUR TABLE?

Could you explain WHY?

As I explained, storing this derived data in your table is *VERY BAD
DESIGN*. Suppose the date field contains #7/19/2005#. You store Yes in
the field. Tommorow that field contains Yes, but that information is
now INCORRECT. Is that what you want?

My suggestion is that this yes/no field need not, and SHOULD NOT,
exist in your table AT ALL. Instead, it should be calculated
dynamically, using a Query. The query I suggested will not store the
data in the table but it *will* correctly calculate the yes/no field.

If I'm missing something, please explain.

John W. Vinson[MVP]