Using IIf null statement in a query

[Guest]

Hi,

I'm trying to write a query that recodes the data that we have; however,
some of the fields are left blank (in the table) for a reason, and I'd like
them to remain blank in the query. I can't seem to get access to ignore the
null fields.

Right now the IIf statement reads:

12a:
IIf([P1T112a]="no",0,IIf([P1T112a]="yes",1,IIf([P1T112a]=Null,Null,-999)))

but this isn't working. Is there another way to write it so that the null
fields are left blank?

Thanks!

 

[Guest]

Try:

IIf(Nz([P1T112a],"",IIf([P1T112a]="no",0,IIf([P1T112a]="yes",1))))

HTH

 

[Tom Ellison]

Dear Philly:

Perhaps a few concepts first would be usable:

- null is not "blank" or "empty"

- to test for null, use "Is Null"

- null means "not answered, not filled in" blank or empty are "", and empty
string, an answer that there is nothing.

Perhaps your test should read:

IIf([P1T112a]="no", 0, IIf([P1T112a]="yes" ,1, IIf([P1T112a] IS Null,
Null, -999)))

Does this help?

Tom Ellison

 

[Guest]

When I tried that, I got an error that said: "The expression you entered has
a function containing the wrong number of arguments"

Try:

IIf(Nz([P1T112a],"",IIf([P1T112a]="no",0,IIf([P1T112a]="yes",1))))

HTH

Hi,

I'm trying to write a query that recodes the data that we have; however,
some of the fields are left blank (in the table) for a reason, and I'd like
them to remain blank in the query. I can't seem to get access to ignore the
null fields.

Right now the IIf statement reads:

12a:
IIf([P1T112a]="no",0,IIf([P1T112a]="yes",1,IIf([P1T112a]=Null,Null,-999)))

but this isn't working. Is there another way to write it so that the null
fields are left blank?

Thanks!

 

[Guest]

Yes, this helps! Thank you so much.

Dear Philly:

Perhaps a few concepts first would be usable:

- null is not "blank" or "empty"

- to test for null, use "Is Null"

- null means "not answered, not filled in" blank or empty are "", and empty
string, an answer that there is nothing.

Perhaps your test should read:

IIf([P1T112a]="no", 0, IIf([P1T112a]="yes" ,1, IIf([P1T112a] IS Null,
Null, -999)))

Does this help?

Tom Ellison

Hi,

I'm trying to write a query that recodes the data that we have; however,
some of the fields are left blank (in the table) for a reason, and I'd
like
them to remain blank in the query. I can't seem to get access to ignore
the
null fields.

Right now the IIf statement reads:

12a:
IIf([P1T112a]="no",0,IIf([P1T112a]="yes",1,IIf([P1T112a]=Null,Null,-999)))

but this isn't working. Is there another way to write it so that the null
fields are left blank?

Thanks!

 

[Guest]

This might fix it...

IIf(Nz([P1T112a],"",(IIf([P1T112a]="no",0,IIf([P1T112a]="yes",1)))))

When I tried that, I got an error that said: "The expression you entered has
a function containing the wrong number of arguments"

Try:

IIf(Nz([P1T112a],"",IIf([P1T112a]="no",0,IIf([P1T112a]="yes",1))))

HTH

Hi,

I'm trying to write a query that recodes the data that we have; however,
some of the fields are left blank (in the table) for a reason, and I'd like
them to remain blank in the query. I can't seem to get access to ignore the
null fields.

Right now the IIf statement reads:

12a:
IIf([P1T112a]="no",0,IIf([P1T112a]="yes",1,IIf([P1T112a]=Null,Null,-999)))

but this isn't working. Is there another way to write it so that the null
fields are left blank?

Thanks!