Add text based on another field (1 Viewer)

H

hmassey

Registered User.

Local time

Today, 06:02

Joined

Feb 21, 2017

Messages

12

• Apr 25, 2017

• #1

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

Wino Moderator

Staff member

Local time

Today, 06:02

Joined

Aug 30, 2003

Messages

36,125

• Apr 25, 2017

• #2

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

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

 

H

hmassey

Registered User.

Local time

Today, 06:02

Joined

Feb 21, 2017

Messages

12

• Apr 25, 2017

• #3

Where would I place the code your suggesting?

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 06:02

Joined

Aug 30, 2003

Messages

36,125

• Apr 25, 2017

• #4

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.

 

H

hmassey

Registered User.

Local time

Today, 06:02

Joined

Feb 21, 2017

Messages

12

• Apr 25, 2017

• #5

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

Wino Moderator

Staff member

Local time

Today, 06:02

Joined

Aug 30, 2003

Messages

36,125

• Apr 25, 2017

• #6

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.

 

H

hmassey

Registered User.

Local time

Today, 06:02

Joined

Feb 21, 2017

Messages

12

• Apr 26, 2017

• #7

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

Wino Moderator

Staff member

Local time

Today, 06:02

Joined

Aug 30, 2003

Messages

36,125

• Apr 26, 2017

• #8

Glad you got it working.