How do I use check box value in expression?

[03SCBeast]

My Code:

REPORT STATUS: IIf(DateDiff("d",Now(),[Report Status])>7,"Current",IIf(DateDiff("d",Now(),[Report Status]) Between 0 And 7,"Due",IIf(DateDiff("d",Now(),[Report Status])<0,"Overdue")))

This code works well in setting field value at "Current" if report is not due for 8 or more days, "Due" if report is due within 7 days, and "Overdue" if report is overdue.

What I want to do is use a checkbox on the overlying form to set the field value to "Current" when it is checked. The check box would indicate the report has been turned in. I've tried to incorporate an "IIF" or "OR" Function (i.e. IIF([Report Status])="-1", "Current" but I keep getting "too many arguments" or some other error. Any help would be appreciated.

 

[sfreeman]

If I understand your question, you now set the value of a text field 'ReportStatus' to "Current" given correct conditions.

You want to change that to a check box, which is a boolean or Yes/No value.

The problem seems to be you are still trying to indicate the true value -1 by setting it to "-1", which is a string or text value.

Try leaving out the quotes...
(i.e. IIF([Report Status])=-1, "Current"......

HTH


Sam_F
"90% of the problem is asking the right question.

 

[03SCBeast]

Thanks for your response.

I'm not sure if I stated my question as clearly as I thought I had so here it goes again.

I have a query based on an underlying table. 3 of the query fields are "Report Due", "Report Status", and "Completed". The following code: Report Status: IIf(DateDiff("d",Now(),[Report Due])>7,"Current",IIf(DateDiff("d",Now(),[Report Due]) Between 0 And 7,"Due",IIf(DateDiff("d",Now(),[Report Due])<0,"Overdue"))) works perfectly in telling me the status of my reports based on the date in the "report due" field.

What I want to do is use a checkbox titled "completed?" in the overlying form to determine if the report has been handed in or not (irrespective of the report status). The check box assigns a -1 or a 0 to the underlying query field "completed". Theoretically, I tried to use the formula below to indicate "Current" in the "Report Status" field if the "Completed" checkbox is checked but proceed to IIf DateDiff evaluations if the checkbox wasn't checked and that's where I get the error messages (I.e, wrong number of arguments, or "ERROR" in "Report Status" Field.

NSP 1 STATUS: IIf([Completed]=-1,"Current",IIf(DateDiff("d",Now(),[Report Due])>7,"Current",IIf(DateDiff("d",Now(),[Report Due]) Between 0 And 7,"Due",IIf(DateDiff("d",Now(),[Report Due])<0,"Overdue"))))

P.S. I thought about doing an If, Then..If, Else statement but not sure which event I would use to trigger evaluation.

 

[PHV]

Try to replace this:
])<0,"Overdue"))))
By this:
])<0,"Overdue","?"))))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[03SCBeast]

PHV...you are officially da' man (or da' woman) for the day. Works flawlessly...thanks for the input.