Help with IIF function

  • Thread starter Thread starter NervousFred
  • Start date Start date
N

NervousFred

I have the following:

IIf([Ownersch].[SMOHL]<1800,1800-[Ownersch].[SMOHL],3600-[Ownersch].[SMOHL])

Everytime I run this it gives me back an #error.

I have messed around with it a bunch and have come to realize that its the
conditional having a less than or equal in it that is causing the error.

Any help to get around this problem?

Thank you
 
Everytime I run this it gives me back an #error.
You did not say what the error was.

Is [Ownersch].[SMOHL] a text or number field?
 
I am running this line in a query and creating a column with it, this peice
of code just one nested part of a larger statment(which all works).

Here is the entire code:

TTHOTL:
iif([Ownerspc].[SHOTL],3600-[Ownerspec].[SHOTL],IIf([Ownersch].[SMOHL]<1800,1800-[Ownersch].[SMOHL],3600-[Ownersch].[SMOHL])

In the column TTHOTL when the original iif statment is false, the cells
display #error inside them.

This is why I am stuck becuase I do not know what the error is.
 
The query doesn't know what "[Ownerspc].[SHOTL]" is.

If "[SHOTL]" is a control or field in a table, try:

TTHOTL:
IIF(Me.SHOTL,3600-Me.SHOTL,IIf(Me.SMOHL<1800,1800-Me.SMOHL,3600-Me.SMOHL)

or

TTHOTL: IIF(SHOTL,3600-SHOTL,IIf(SMOHL<1800,1800-SMOHL,3600-SMOHL)


If "[SHOTL]" is a calculated control in the query, you have to use the
calculation, not the alias. For example, if the calculated control "[SHOTL]"
is

SHOTL: (Field2*10) +50

then the TTHOTL column would look like:

TTHOTL: IIF((Field2*10) +50, 3600-(Field2*10) +50, IIf((Field2*10)
+50<1800,1800-(Field2*10) +50,3600-(Field2*10) +50)


Something else to look into. Refering to the following:

TTHOTL: IIF(SHOTL,3600-SHOTL,IIf(SMOHL<1800,1800-SMOHL,3600-SMOHL)

AFAIK, the only time that the nested IIF() will return a value, is if
"SHOTL" is equal to ZERO. In Access, FALSE = 0 and TRUE = -1. Actually, TRUE
is any non-Zero number. Access is not on the computer I am using, so I can't
check this. But that is what I remember reading in a posting a looooong time
ago.

HTH
 
Steve,

I have tried both ways you have posted. Still have the same issue will all
the false cases coming up with errors but the true case working.

SHOTL is a controled field created with no calculations and the formula
requires the [Ownerspc] and [Ownersch] because I have multiple tables with
[SMOHL] and [SHOTL]

It might be a problem with what you said at the end of your post with IIF
needing a zero. The false cases are when SHOTL are equal to blank("").
Could this be causing the problem and if so is there a way to fix it?
 
In a previous post you provided:

TTHOTL:
iif([Ownerspc].[SHOTL],3600-[Ownerspec].[SHOTL],IIf([Ownersch].[SMOHL]<1800,1800-[Ownersch].[SMOHL],3600-[Ownersch].[SMOHL])

I see three different table names:

Ownerspc
Ownerspec <= is this a table name?
Ownersch


I made two tables and a query. I finally got it to return a value by
aliasing the field names.

In the query, where the table name is "Ownersch", I set the field name to
"CH: SMOHL". And for the table name "Ownerspc", I set the field name to
"PC: SMOHL" . (no quotes).

For the column "TTHOTL", I used:

TTHOTL: IIf([PC],3600-[PC],IIf([CH]<1800,1800-[CH],3600-[CH]))


when SHOTL are equal to blank
Click to expand...

Might these cases be NULLs? If so, you might try:

TTHOTL: IIf(NZ([PC],0),3600-[PC],IIf([CH]<1800,1800-[CH],3600-[CH]))

or

TTHOTL: IIf(NZ([PC],0)<>0,3600-[PC],IIf([CH]<1800,1800-[CH],3600-[CH]))



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


NervousFred said:
Steve,

I have tried both ways you have posted. Still have the same issue will all
the false cases coming up with errors but the true case working.

SHOTL is a controled field created with no calculations and the formula
requires the [Ownerspc] and [Ownersch] because I have multiple tables with
[SMOHL] and [SHOTL]

It might be a problem with what you said at the end of your post with IIF
needing a zero. The false cases are when SHOTL are equal to blank("").
Could this be causing the problem and if so is there a way to fix it?
Click to expand...
 
Steve,

Yes Ownerspec is not a field, I need to watch my typos.

I have tried everything you mentioned and am still getting an error on the
false case.

When PC is false it is a NULL becuase I have do not have a value for that
specific cell.

What I don't understand is that if I use

TTHOTL: IIf(NZ([PC],0)<>0,3600-[PC],IIf([CH]<1800,1800-[CH],3600-[CH]))

then it should change all the blanks in PC to 0 and then complete the rest
of the IFF statment as if PC contained 0's not blanks but am still getting
errors when ever the flase case is used.
 
You could change the column to

TTHOTL: IIf(NZ([PC],0)<>0,3600-[PC],IIf([CH]<1800,18000,36000))

and see if you still get the error. (NOTE: change the 1800 and 3600 to
18,000 and 36,000)

Or you could try:

TTHOTL: IIf(NZ([PC],0)<>0,3600-[PC],IIf([CH]<1800,1800,3600)-[CH])


Or add a new column to test the false condition:

TT_Test: IIf(NZ([PC],0)<>0,-1111, IIf([CH]<1800,1800,3600)-[CH])


Is there any chance that CH could have NULL values??

Or post the SQL of your query and some values for PC and CH and the results
you expect......

HTH
 
Back
Top