By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,521 Members | 1,274 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,521 IT Pros & Developers. It's quick & easy.

Problem with MS Access calculated text box

P: 10
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”,”Not 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
Mar 9 '10 #1

✓ answered by yarbrough40

its all in the parentheses as you can see... play with that. right now I don't have an instance of Access to play around with to test it.
try this one:
Expand|Select|Wrap|Line Numbers
  1. = IIf(IsNull([Response received]), ”Processing”, IIf([Response received] > [Reply due by], "overdue", "On Time"))

Share this Question
Share on Google+
19 Replies


yarbrough40
100+
P: 320
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.
Mar 9 '10 #2

P: 10
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.
Mar 9 '10 #3

100+
P: 122
=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?
Mar 9 '10 #4

yarbrough40
100+
P: 320
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 --->
Expand|Select|Wrap|Line Numbers
  1.  
  2. =IIF([Response received]>[Reply due by],”Overdue”,”Not completed”)
  3.  
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.
Mar 9 '10 #5

P: 10
Thanks Gershwyn and Yarbrough40!

Yarbrough40, I have entered =IIf([Response received]>[Reply due by],"overdue","processing") 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 “processing” when I would rather they came up as "completed". Is there a way to have 3 possible outcomes?
Mar 9 '10 #6

yarbrough40
100+
P: 320
yes
Expand|Select|Wrap|Line Numbers
  1. IIF ([Response received]<=[Reply due by],”On Time”, (IIf([Response received]>[Reply due by],"overdue","processing")))
  2.  
Mar 9 '10 #7

P: 10
Thanks that now comes up with #Name?
Mar 9 '10 #8

yarbrough40
100+
P: 320
(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.
Expand|Select|Wrap|Line Numbers
  1. =IIf IsNull([Response received]),”Processing”, (IIf([Response received]>[Reply due by],"overdue","On Time"))) 
  2.  
Mar 9 '10 #9

P: 10
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"
Mar 9 '10 #10

yarbrough40
100+
P: 320
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 : )
Mar 9 '10 #11

P: 10
ok, thank you, I am going to play with this for a bit and get back to you!
Mar 9 '10 #12

yarbrough40
100+
P: 320
excellent.... don't hesitate to post back if you absolutely cannot get it.
Mar 9 '10 #13

P: 10
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! :-)
Mar 9 '10 #14

yarbrough40
100+
P: 320
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)
Mar 9 '10 #15

P: 10
I have tried all morning to do this on your theory: =IIF(boolean condition, result if true, result if false) but no matter how I tweak it, it still returns an error. I really thought it was a problem wit th brackets, but I have tried moving and adding, but nothing works, the original IIF in the statement is exactly the same as the old one, so that's not the problem...help...please?
Mar 10 '10 #16

yarbrough40
100+
P: 320
Expand|Select|Wrap|Line Numbers
  1.  =IIf (IsNull([Response received]),”Processing”, (IIf([Response received]>[Reply due by],"overdue","On Time"))))
Mar 10 '10 #17

P: 10
Thanks, tried it, but I get the error message: The expression you entered has too many closing parenthesis
Mar 10 '10 #18

yarbrough40
100+
P: 320
its all in the parentheses as you can see... play with that. right now I don't have an instance of Access to play around with to test it.
try this one:
Expand|Select|Wrap|Line Numbers
  1. = IIf(IsNull([Response received]), ”Processing”, IIf([Response received] > [Reply due by], "overdue", "On Time"))
Mar 10 '10 #19

P: 10
I got there in the end, this worked:
Expand|Select|Wrap|Line Numbers
  1. =IIf(IsNull([Response Received]),"Processing",IIf([Response Received]>[Reply due by],"overdue","on time"))
Thanks for all your help!
Mar 23 '10 #20

Post your reply

Sign in to post your reply or Sign up for a free account.