Help with IIF function

[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

 

[KARL DEWEY]

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?

 

[NervousFred]

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.

 

[KARL DEWEY]

I repeat --- Is [Ownersch].[SMOHL] a text or number field?

 

[Steve Sanford]

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

 

[NervousFred]

Sorry that I missed it the first time, [Ownersch].[SMOHL] is a number field

 

[NervousFred]

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?

 

[Steve Sanford]

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

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

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?

 

[NervousFred]

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.

 

[Steve Sanford]

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