iif clause

[Frank]

I have a query that reflects a field named phonenumber on the main tale. I
want to replace all null values with "-----". my iif clause is as follows:

iif([phonenumber] is null, "----", [phonenumber]). When I run the query, it
returns all not null values. What am I doing wrong.

Any assistance is greatly appreciated.

 

[Marshall Barton]

I have a query that reflects a field named phonenumber on the main tale. I
want to replace all null values with "-----". my iif clause is as follows:

iif([phonenumber] is null, "----", [phonenumber]). When I run the query, it
returns all not null values. What am I doing wrong.

That looks like it should work as a calculated field. Make
sure you do not have a criteria ;ile Is Not Null on the
phone number field.

 

[Frank]

Thanks for assistance. However, the problem still persist. Each time I run
the query, it does not populate null values with ------, it just returns all
"Non Null" values. I do not have a non null value on the phonenumber field.
In fact, I created a new query with just the phonenumber field and put the
same iif clause statement on it. The results were the same. Wonder why it
won't work?

 

[John W. Vinson]

I have a query that reflects a field named phonenumber on the main tale. I
want to replace all null values with "-----". my iif clause is as follows:

iif([phonenumber] is null, "----", [phonenumber]). When I run the query, it
returns all not null values. What am I doing wrong.

Any assistance is greatly appreciated.

Hard to tell what you're doing wrong because it's not clear what you're doing!
Please post the SQL view of your query. What's the context of this IIF, in
other words?

 

[Linq Adams via AccessMonster.com]

In VBA the syntax is not

iif([phonenumber] is null, "----", [phonenumber]).

but rather

iif(IsNull([phonenumber]), "----", [phonenumber]).

 

[Frank]

Hi John

the sql view is as follows:

SELECT [Main11-09].HomePhone
FROM [Main11-09]
WHERE ((([Main11-09].HomePhone)=IIf([homephone] Is
Null,"_____",[homephone])));


The table has about 2000 records in which the homephone field is null. I
want to fill all null values in this field with "_____".
The main reason for this is so that a report built on the query will not
reflect blank spaces but will be filled in with ______. In that way, the
report looks much better.

Any suggestions?

I have a query that reflects a field named phonenumber on the main tale.
I
want to replace all null values with "-----". my iif clause is as follows:

iif([phonenumber] is null, "----", [phonenumber]). When I run the query,
it
returns all not null values. What am I doing wrong.

Any assistance is greatly appreciated.

Hard to tell what you're doing wrong because it's not clear what you're
doing!
Please post the SQL view of your query. What's the context of this IIF, in
other words?

 

[Marshall Barton]

Thanks for assistance. However, the problem still persist. Each time I run
the query, it does not populate null values with ------, it just returns all
"Non Null" values. I do not have a non null value on the phonenumber field.
In fact, I created a new query with just the phonenumber field and put the
same iif clause statement on it. The results were the same. Wonder why it
won't work?

I have a query that reflects a field named phonenumber on the main tale. I
want to replace all null values with "-----". my iif clause is as follows:

iif([phonenumber] is null, "----", [phonenumber]). When I run the query,
it returns all not null values. What am I doing wrong.

Maybe the phone field in the table is a Text field with its
AllowZeroLength property set to Yes? If so, the field is
probably equal to "" instead of being Null.

Unless you have a very good reason for it, you probably
should set that property to No.

If you do have a good reason, you can check for both Null
and ZLS by using:
IIf(Nz(phonenumber, "")= "", "----", phonenumber)

OTOH, maybe you have users that enter one or more spaces
when they don't know the phone number.

If I'm barking up the wrong tree, please post a COPY/PASTE
of your query's SQL view and provide details about the
phonenumber field.

 

[Frank]

Tried the new syntax yet without success. Created a new database with just
the table and query. Still can not get it to return null values.

wonder what is wrong?

 

[John W. Vinson]

Hi John

the sql view is as follows:

SELECT [Main11-09].HomePhone
FROM [Main11-09]
WHERE ((([Main11-09].HomePhone)=IIf([homephone] Is
Null,"_____",[homephone])));


The table has about 2000 records in which the homephone field is null. I
want to fill all null values in this field with "_____".
The main reason for this is so that a report built on the query will not
reflect blank spaces but will be filled in with ______. In that way, the
report looks much better.

Your query puts the IIF in the Criteria - that's the wrong place. It will find
all records wher the HomePhone is equal to the text string "______" if it's
NULL, or equal to itself if it's not: i.e. it will find only non-NULL phones.

Instead, use the NZ() function to *display* the underscores if the field is
null:

SELECT NZ([Main11-09].HomePhone, "______")
FROM [Main11-09];

 

[Frank]

Hi John:
Was able to get expression to work using the Nz expression. Thanks again for
all of your assistance.