nested iif function

P

pat67

Hello, I am getting an error when I insert a nested iif function and I
can't understand why. Can someone help? Here is the issue. I have a
field which shows either a number or the word "Late". So my function
is as follows:

iif([Days From Extraction]>35,">35 Days",iif([Days From Extraction]
<=35,"<=35 Days","Late"))

"Days From Extraction" is the number of days an order is due from when
i run the query. If the due date is in the past, it shows "Late". When
i then enter another field with the nested funtion, whenever it should
say "Late" it is showing "#Error"

Help please.

Thanks
 
R

Roger Carlson

I don't understand how it can ever show "Late". Either a number is >35 or
it is <=35. "Late" can never come up. I'm missing something here.
 
D

Daryl S

Pat -

What type of field is [Days from Extraction]? Is this the field that will
have either a number or the word "Late"? If so, this is a text field, and
your numeric comparisons will not work. In this case, try the following:

iif([Days From Extraction]= "Late","Late",iif(val([Days From Extraction])
<=35,"<=35 Days","Late"))
 
K

KARL DEWEY

I concur.
Post how you used another field.


--
Build a little, test a little.


Roger Carlson said:
I don't understand how it can ever show "Late". Either a number is >35 or
it is <=35. "Late" can never come up. I'm missing something here.


--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com
http://rogersaccessblog.blogspot.com/



pat67 said:
Hello, I am getting an error when I insert a nested iif function and I
can't understand why. Can someone help? Here is the issue. I have a
field which shows either a number or the word "Late". So my function
is as follows:

iif([Days From Extraction]>35,">35 Days",iif([Days From Extraction]
<=35,"<=35 Days","Late"))

"Days From Extraction" is the number of days an order is due from when
i run the query. If the due date is in the past, it shows "Late". When
i then enter another field with the nested funtion, whenever it should
say "Late" it is showing "#Error"

Help please.

Thanks


.
 
P

pat67

I concur.

Post how you used another field.

--
Build a little, test a little.



Roger Carlson said:
I don't understand how it can ever show "Late".  Either a number is >35 or
it is <=35.  "Late" can never come up.  I'm missing something here.
pat67 said:
Hello, I am getting an error when I insert a nested iif function and I
can't understand why. Can someone help? Here is the issue. I have a
field which shows either a number or the word "Late". So my function
is as follows:
iif([Days From Extraction]>35,">35 Days",iif([Days From Extraction]
<=35,"<=35 Days","Late"))
"Days From Extraction" is the number of days an order is due from when
i run the query. If the due date is in the past, it shows "Late". When
i then enter another field with the nested funtion, whenever it should
say "Late" it is showing "#Error"
Help please.
Thanks
.- Hide quoted text -

- Show quoted text -

Days from Extraction has numbers and text. there is an iif function -
iif([Item Delivery]>[Extraction Date],[Item Delivery]-[Extraction
Date],"Late")

this shows orders already late vs what's still due. I am trying to
group them so there is a percentage of the 3 parameters I have.

I got it to work by doing the following

IIf([Item deliv]-[Extraction Date]>35,">35 Days",IIf([Item deliv]<
[Extraction Date],"Late","<=35 Days"))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top