R
rdunne
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
higher or lower, putting the answer in another field
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
T
Topcat
Just a small tip - use the DateSerial function to make your comparisons in
code, especially if you are not using the US date format
code, especially if you are not using the US date format
K
KARL DEWEY
UPDATE YourTable SET YourTable.[High-Low] =
IIf([YourDate]>Date(),"High","Low");
IIf([YourDate]>Date(),"High","Low");
J
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]
R
rdunne
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 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.
John Vinson said: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]
J
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]