Access Textbox Conditional Formatting based on other Textbox value

AkAlan
AkAlan used Ask the Experts™
on
I ave an access form with two date textboxes and I want one textbox to display the current date if another textbox is NULL. I have made several attempts but can't get this to work. Here is what I think should work but doesn't:

In the textbox NextDueDate I put this in the expression box after Expression Is:

IIf(IsNull([LastDateCompleted]),Now(),[NextDueDate])
Also
IIf(IsEmpty([LastDateCompleted]),Now(),[NextDueDate])
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Architect / Application Developer
Top Expert 2007
Commented:
Instead of Conditional Formatting, just make this the Control Source for the textbox:

=IIf(IsNull([LastDateCompleted]),Now(),[NextDueDate])

including the equals sign.

mx

Commented:
Unless it's a bound control, in which case you may want a variation of that code in the appropriate event (current? after_update? before_insert?)

If (IsNull(me.[LastDateCompleted]) then
     [NextDueDate] = now()
end if
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
How would me.[LastDateCompleted] work in Conditional Formatting, if that is what you are saying ?

mx

Commented:
mx,
Not sure I understand your question.  It's not Conditional Formatting in the sense that I normally use the term (changing visual properties of a control based on it's contents).  I'm just changing the value of one control (and its underlying field in the case of a bound column) based on the contents of another.

I wasn't disputing your answer, just offering an alternative in case the Control Source was tied to a field in the recordsource.  I see you got the points, so I guess your solution was on the mark.



DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Sorry ... misread what you were saying ...

mx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial