473,326 Members | 2,102 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Problem with MS Access calculated text box

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"))

19 4171
yarbrough40
320 100+
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
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
gershwyn
122 100+
=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
320 100+
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
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
320 100+
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
Thanks that now comes up with #Name?
Mar 9 '10 #8
yarbrough40
320 100+
(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
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
320 100+
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
ok, thank you, I am going to play with this for a bit and get back to you!
Mar 9 '10 #12
yarbrough40
320 100+
excellent.... don't hesitate to post back if you absolutely cannot get it.
Mar 9 '10 #13
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
320 100+
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
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
320 100+
Expand|Select|Wrap|Line Numbers
  1.  =IIf (IsNull([Response received]),”Processing”, (IIf([Response received]>[Reply due by],"overdue","On Time"))))
Mar 10 '10 #17
Thanks, tried it, but I get the error message: The expression you entered has too many closing parenthesis
Mar 10 '10 #18
yarbrough40
320 100+
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
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

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

Similar topics

1
by: algebraist | last post by:
hi, newbie question here i play poker on partypoker and ultimate bet (both of which have clients you have to download)... i was thinking of writing a program that used the hand histories these...
8
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...
3
by: nick | last post by:
Is possible for server side code to access the text of <asp:label> changed by client side javascript code?
4
by: Ken Soenen | last post by:
The code below illustrates my problem which is: I'm trying to access the TEXT from TextBox1 which is on Form1. Line "aa = Form1.TextBox1.Text" produces the error--Reference to a non-shared member...
1
by: administrator | last post by:
Hi, I'm having trouble with a calculated text box. "= & & & & & & & & & & & & & & " This produces the correct results, but I want it to create each outcome below the previous...
2
by: Olveres | last post by:
Hi, I have managed to work out how to add new lines into a calculated text box. However in this text box some of the outcome fields are empty however when previewing the report it includes the...
11
by: viki1967 | last post by:
Problem with output text Hi all. I have this page ASP: <!-- #include virtual="/include/conn_mysql.asp"--> <%
9
by: dombrost | last post by:
Hi all. I am total newbie to Access so any help is greatly appreciated. I am creating a simple database in Access 2003 that captures details of an operation event. I created a form for the user...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.