if-then statement in criteria

[robbin]

I am trying to write a double statement in the criteria of my query. If the sodium level is <135 or >145 then print the value, but if it is between 135-145 print "-".
How do I say this ??

 

[Drew]

Hi Robbin,

rather than do it in the criteria you want to do it in the Field of the query. You would put in something like -

SodiumLevelIs: iif([SodiumField]<135 Or SodiumField>145,SodiumField,"-")

HTH

Drew

 

[robbin]

It is saying "undefined if in expression"

 

[Abby N]

Are you using IIF or IF in your expression. If you're using IF, change it to IIF and you should be ok. However, if that doesn't solve the error post your expression here and we'll get it sorted out.

~Abby

[This message has been edited by Abby N (edited 08-23-2001).]

 

[robbin]

thanks
I changed it to IIF (it comes out IIf)
the error message is gone
but in the query results it says ERROR and no data at all in the column.

this is exactly what I have

Expr1: IIf([preop sodium]<135 Or [preop sodium]>145,[preop sodium],"-")
what am I doing wrong ?

 

[Abby N]

The query may be looking to itself for the value of [preop sodium]. Try either adding [preop sodium] to the query or changing your expression to:

Expr1: IIf([TableName]![preop sodium]<135 Or [TableName]![preop sodium]>145,[TableName]![preop sodium],"-")

~Abby

 

[robbin]

sorry
this is exactly what I now have
Expr1: IIf([3 laboratory values]![preop sodium]<135 Or [3 laboratory values]![preop sodium]>145,[3 laboratory values]![preop sodium],"-")
and I still get the "#error" in the column
This is just not cooperating.
How do I add [preop sodium] to the query?

 

[Abby N]

Odd. Lets start with the basics. First, try this:

IIf(Cdbl([3 laboratory values]![preop sodium])<135 Or Cdbl([3 laboratory values]![preop sodium])>145,[3 laboratory values]![preop sodium],"-")

If that doesn't work, would you mind answering these questions?

1) Is [3 laboratory values] a table or query?
2) What is the data type for [preop sodium]?
3) Is [3 laboratory values] included in your query as a source table?

~Abby

 

[robbin]

It worked!!
thank you very much
now I have 8 more to do
so I will try and copy what you have given me to get it all working
thank you

 

[Dawnit]

What is the Cdbl for in the IIf(Cdbl([3 laboratory... formula above?

 

[Abby N]

The CDbl function converts text to a double. Say you have a text field with a value of "10.5" and try to add 4.5. You'll get and error because "10.5" is text. However CDbl("10.5") + 4.5 = 15.

In the equation I suggested to Robbin I included it because [preop sodium] as a text field was the most likely cause of an error I could think of.

~Abby