Sabtu, 17 September 2011

RE: [MS_AccessPros] Confusion with use of "Is Null" and :Nz"

Add this column:
Wdth: Len(PhoneAH)
You can try
Result: IIf(Len(Trim([PhoneAH]))=0,"Unknown",Format([PhoneAH],"@;""ZLS"""))

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: robinski@mymail.net.au
Date: Sun, 18 Sep 2011 11:19:32 +1000
Subject: RE: [MS_AccessPros] Confusion with use of "Is Null" and :Nz"


Douane,

From your remark about "zero-length-string" I have now found some
advice on the Microsoft site and added this to my query:

Result: IIf(IsNull([PhoneAH]),"Unknown",Format([PhoneAH],"@;""ZLS"""))

I have added an image to the page:

<http://www.rotary9790.org.au/test/test.asp>http://www.rotary9790.org.au/test/test.asp

and the result is unexpected!

Many thanks,

Robin Chapple

At 18/09/2011 10:42 AM, you wrote:

>Robin,
>If the PhoneAH is truly null then I think all you need is:
>Phonz: [PhoneAH] + " AH"
>
>Is it possible the field might be a zero-length-string or something
>you can't see?
>
>Duane Hookom
>MS Access MVP
>
>
>
>
>
>To: MS_Access_Professionals@yahoogroups.com
>From: robinski@mymail.net.au
>Date: Sun, 18 Sep 2011 09:28:53 +1000
>Subject: [MS_AccessPros] Confusion with use of "Is Null" and :Nz"
>
>
>
>
>
>
>I have been advised about the use of Nz but he code to use has ben supplied.
>
>I have not learned!
>
>This code in a query:
>
>Phonz: IIf([PhoneAH] Is Null,"",([PhoneAH] & " AH"))
>
>still gives AH in cases where I have dirty data.
>
>I Have learned that this is wrong:
>
>Phonz: IIf([PhoneAH] Is Null,"",(Nz[PhoneAH] & " AH"))
>
>Where is the correct place to use the Nz?
>
>Many thanks,
>
>Robin Chapple
>
>
>
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>


[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Tidak ada komentar:

Posting Komentar