Full Version: Control Source In Fieled On Form
UtterAccess Forums > Microsoft® Access > Access Forms
I have a field on a form that has a control source equal to IIf(Int([Due Date])<Int(Date()),"Overdue"). This command works OK if the current date exceeds the Due Date. I also have a field on that same form called Completed Date. Once the completed date is filled in, even though the date may exceed the Due Date, I want the Overdue notice to disappear. Can anyone give me the proper language to do that please? Thanks in advance for any help.
You're IIF() function is incomplete. There are 3 parts to an IIF() function IIF(expression, true part, false part). You should be able to add the false part to get what you want :
IIf(Int([Due Date])<Int(Date()),"Overdue", "Not Overdue")
For if you do not want to show anything:
IIf(Int([Due Date])<Int(Date()),"Overdue", Null)
If you want the control to respond to both the Due Date and the Complete Date your IIF() function will be more complex. Under what conditions of due and complete date would you want the overdue to appear versus something else?
Bob G
It would be something like this air code. It would depend on the data type of the completed date field. but, regardless, i think you catch my drift
iif(isempty([completed date]),(IIf(Int([Due Date])<Int(Date()),"Overdue","completed"))
I want the Overdue note to appear if the current date exceeds the due date and the Completed Date is empty. Once the Completed Date is filled in, I want the Overdue note to disappear. Thanks.
You could try what Bob suggested with a little tweak
if(IsEmpty([completed date]) AND Int([Due Date])<Int(Date()),"Overdue",NULL))
You may also look at the IsNull() or NZ() functions in place of the IsEmpty() function depending on the datatype of the completed date field
Logically, you should not need to use Int() with the 2 date values, even if you want to compare without the time component. Try:
=IIf((Not IsDate([Completed Date])) And ([Due Date]<Date()),"Overdue","")
To vtd: Your code worked!! Thanks so much for your time. Can you recommend a good guidance manual/document on logical statements like the one you did?
You're welcome... Glad we could help...
Any Boolean Algebra book will do and in fact, you probably need to read only the first few chapters. If you have any Discrete Mathematics book, the section on Boolean Algebra is probably enough provided that it covers de Morgan's laws.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.