iif clause
in [Forms]
|
Prev: delete button deletes the wrong thing
Next: Odd Error
From: Frank on 10 Feb 2010 16:57 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.
From: Marshall Barton on 10 Feb 2010 17:42 Frank wrote: >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. -- Marsh MVP [MS Access]
From: Frank on 10 Feb 2010 18:22 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? "Frank" <fhsmith(a)cox.net> wrote in message news:iZFcn.48387$zN4.39188(a)newsfe05.iad... >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. >
From: John W. Vinson on 10 Feb 2010 18:45 On Wed, 10 Feb 2010 15:57:26 -0600, "Frank" <fhsmith(a)cox.net> wrote: >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? -- John W. Vinson [MVP]
From: Linq Adams via AccessMonster.com on 10 Feb 2010 18:53
In VBA the syntax is not iif([phonenumber] is null, "----", [phonenumber]). but rather iif(IsNull([phonenumber]), "----", [phonenumber]). -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201002/1 |