IIF statment criteria Numeric

[cardonas]

Hello Everybody,

I did several different IF statements in access and they work perfectly! But as soon as I got to my last if statement it query on a field that can a numeric or just null or " ". I wrote the IF statement below and it seems to work perfectly if I take out the "GREATER then 9" part but I need that . My query runs and the results in the field [HbA1c Test Value] for blanks I get "0" and for Null I get "0" and if the cell contains a numeric value I get an error??? Not sure what I am doing wrong

Basically I am looking for a patients
Qualified 2015? = yes or no visit
HbA1c Confirmed= yes or no visit
HbA1c Value= "null' or " " or is greater then 9

If the above criteria is met then I want a "0" to show up if not then a "1" will show up in the line

here is my If statement



Test?: IIf(([Qualified for 2015?]="Yes" Or [Qualified for 2015?]="No visit in 2015") And ([HbA1c Test Performed]="Yes" Or [HbA1c Test Performed] Is Null Or [HbA1c Test Performed]=" ") And ([HbA1c Test Value]Is Null Or [HbA1c Test Value]=" " Or [HbA1c Test Value]>9),"0","1")


I am just not understanding why the numeric fields are pulling in an error and not going by the greater by? Any the field I am criteria is numeric so all format matches. Any help will greatly be appreciated

 

[CJ_London]

the field [HbA1c Test Value] is either text or numeric - it can't be both.

if text then try

val([HbA1c Test Value])>9

Note that [HbA1c Test Value]>"9" won't work because in text "9" is greater than "10"

 

[arnelgp]

And (VAL("" & [HbA1c Test Value])=0 OR VAL("" & [HbA1c Test Value])>9))

 

[cardonas]

Thank you guys so much!! I had no idea it could not be both good to know and thanks again I did what you guys suggested and it work!!! now I can get my scorecard in yay!!!