Problem with MS Access calculated text box

[homeownership]

I have a database which I need to run a report of items which are “Overdue” or “Not completed”. I need to set up a new field (status) so that it can tell me if an item is Overdue or not completed. I was doing a calculated text box and inside the text box I entered the following first:

=IIf(([Date emailed enquiry]+[Response time])<=[Response Received],"Overdue"," Not completed")

However this was not entering accurate information in the status field. E.g. I had items which were responded to on time, however they were coming up as “Not comleted”.

I then tried the following:
=IIF(([Response received]<=([Date emailed enquiry]+[Response time]),”Overdue”,”No t completed”)
But this comes back as: The expression you entered contains invalid syntax. You may have entered an operand without an operator.

Can anyone see where I am going wrong at all or offer me any advice?

Thank you

[yarbrough40]

can you show me the values and field formats for the fields : [Date emailed enquiry],[Response time],[Response Received] that you are referencing in the table? I suspect your problem lies there.

[homeownership]

Thanks for your response, they are all date formats apart from [Response time] which is a number between 1 and 14.

I have a field called [Reply due by] which is a calculated field and that is worked out by =[Date emailed enquiry]+[Response time]

And what I need is a new field with status which will give me "Overdue" or "Not completed" when I work out [Response Received] against [Reply due by]

Hope this makes sense.

[gershwyn]

=IIf(([Date emailed enquiry]+[Response time])<=[Response Received],"Overdue"," Not completed")

However this was not entering accurate information in the status field. E.g. I had items which were responded to on time, however they were coming up as “Not comleted”.

Can you explain what you're trying to do here? That statement only has two possible responses: overdue or not completed. What do you want to happen if the response was received on time?

[yarbrough40]

ok then I have two thoughts here (you haven't shown me actual values for these fields so not sure which one you need)
---also Gershwyn brings up a good point ( are you sure you are labeling the results correctly?)

1)
if [Reply due by] is already calculated as you say then you shouldn't need to calculate it again. just call the value.
try this --->

 
=IIF([Response received]>[Reply due by],”Overdue”,”Not completed”)

2)
depending on what your actual date values are for [Response received] and [Reply due by] you may be confused at your answer because when adding a number to a date by default it adds a full day. the time stamp portion of the date remains the same even though it may not be visible when you view it because the readable format of the date may leave off the time.

[homeownership]

Thanks Gershwyn and Yarbrough40!

Yarbrough40, I have entered =IIf([Response received]>[Reply due by],"overdue","pro cessing") and it works!!!

HOWEVER, Gershwyn has made a valid point which I did not think about, the ones which were received on time are still coming up as “processin g” when I would rather they came up as "completed" . Is there a way to have 3 possible outcomes?

[yarbrough40]

yes

IIF ([Response received]<=[Reply due by],”On Time”, (IIf([Response received]>[Reply due by],"overdue","processing")))

[homeownership]

Thanks that now comes up with #Name?

[yarbrough40]

(you probably forgot your "=" sign like I did)
or maybe this is what you want... the important thing is do you understand the concept? you may need to tweak the code to get it exact.

=IIf IsNull([Response received]),”Processing”, (IIf([Response received]>[Reply due by],"overdue","On Time")))

[homeownership]

To be perfectly honest, I understand:
=IIF([Response received]>[Reply due by],”Overdue”, ”Not completed”)
this makes sense to me, but by adding in the additional "status" (Overdue, On time, Completed) I have really got confused. I think its too complex for me.

I have entered it, and it now says that "You must enclose IIF function arguments in parentheses"

[yarbrough40]

It is really not complicated at all... all you are doing is putting a conditional statement (IIF) within another conditional statement.
the IIF statement must look like this

=IIF(boolean condition, result if true, result if false)

all I have done is replace the result if false with another IIF condition. this second IIF condition must also follow the same exact format on its own.

the error you are getting is saying that you don't have all the parentheses you need or you typed them wrong. ... I already see the mistake I made.... you can get this I promise : )

[homeownership]

ok, thank you, I am going to play with this for a bit and get back to you!

[yarbrough40]

excellent.... don't hesitate to post back if you absolutely cannot get it.

[homeownership]

I absolutely cannot get it. I thought maybe it was an issue with the brackets, but when I made changes there, I made it worse. I finish at 4.30, so if you are feeling generous can you send me a hint! Or just post me the solution!!! Thanks! :-)

[yarbrough40]

The last code I gave you is one big IIF statement. (don't let the fact that there is another IIF statement embedded in it confuse you)

just make sure the whole statement follows the format:
=IIF(boolean condition, result if true, result if false)