Add text based on another field

[hmassey]

Is it possible to add text to a filed based on another field.
Specifically I want to add the word YES or NO to a filed based on a date. If today is before the date then put YES, if today is after the date then put NO, if the date field is empty then do nothing.

I would like this to automatically happen every time a new record is added to the table.

I initially tried to use conditional formatting on the date filed, but it wasn't on the new records created after applying the conditional formatting.

 

[pbaldy]

I probably wouldn't store the value. You can display it with:

IIf(IsNull([DateField]), "", IIf(Date<[DateField], "Yes", "No"))

 

[hmassey]

Where would I place the code your suggesting?

 

[pbaldy]

Wherever you want it. It's just a formula; it could be in the control source of a textbox, VBA code, a query, etc. If you're using a recent version, it could probably be a calculated field in a table.

 

[hmassey]

I can't quite get it to work.
I tried adding it as a calculated filed in the table and it gave me the message "The expression cannot be used in a calculated filed."
I tried putting it in the control source for the textbox where the yes or no should be and nothing happened.
I don't know which event to put the expression on for VBA.

 

[pbaldy]

I don't use calculated fields, so perhaps IsNull() can't be used in one. In a textbox, it would need an = first, and of course you'd have to change the field name as appropriate.

 

[hmassey]

I got it.
I removed the IsNull expression, placed the new formula in the text box on the form and viola. The word yes or no now appears automatically in the desired field.
pdaldy - Thanks so much for you help.

 

[pbaldy]

Glad you got it working.