iif clause
in [Forms]
|
Prev: delete button deletes the wrong thing
Next: Odd Error
From: Frank on 10 Feb 2010 18:59 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? "John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:o7h6n5lhgm97kqurjd0ffdrv399amlc6c0(a)4ax.com... > 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: Marshall Barton on 10 Feb 2010 19:08 Frank wrote: >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" 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. 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. -- Marsh MVP [MS Access]
From: Frank on 10 Feb 2010 19:36 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? "Linq Adams via AccessMonster.com" <u28780(a)uwe> wrote in message news:a3726752d4ec7(a)uwe... > 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 >
From: John W. Vinson on 10 Feb 2010 20:33 On Wed, 10 Feb 2010 17:59:29 -0600, "Frank" <fhsmith(a)cox.net> wrote: >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]; -- John W. Vinson [MVP]
From: Frank on 11 Feb 2010 13:19
Hi John: Was able to get expression to work using the Nz expression. Thanks again for all of your assistance. "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. > |