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

Minggu, 18 September 2011

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

Hi Robin

You are comparing apples and oranges here - you are displaying the *value*
of [PhoneBH] and the *length* of [PhoneAH].

Try:
SELECT tMembers.MemberID, tMembers.PhoneAH, Len([PhoneAH]) AS Wdth
FROM tMembers
ORDER BY Len([PhoneAH]);

It seems to me you have two problems here - one is that the fields are
padded with blanks, and the other (possibly) is that they allow zero length
strings. I think it is good practice to disallow ZLS data in text fields,
because a ZLS looks just like a Null, but is different. Disallowing ZLS
makes the check for "empty" fields much easier.

I suggest you run two update queries:
UPDATE tMembers SET [PhoneAH] = Trim([PhoneAH]);
and then:
UPDATE tMembers SET [PhoneAH]=Null where Len([PhoneAH])=0;

Run these two queries and then repeat them for each of your other phone
fields. Then change the design or tMembers to disallow ZLS for each of
these fields.

You will then be sure that any field that *looks* empty in fact contains
Null. Then this expression will work:

PhoneAHz : ([PhoneAH] + " AH")

The reason this works is because of a phenomenon known as "null
propagation", where a Null value combined with any operator except &
produces a Null result. In summary:

"ABC" & "xyz" gives "ABCxyz"
"ABC" + "xyz" gives "ABCxyz"
Null & "xyz" gives "xyz"
Null + "xyz" gives Null

Best wishes,
Graham

> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
> Sent: Monday, 19 September 2011 10:26
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Confusion with use of "Is Null" and :Nz"
>
> In the database the field "PhoneAH" is one of four telephone number
> fields. Another is "PhoneBH" and I have set up the query to update
> the fields with a width of "3" to find the display shown here:
>
>
<http://www.rotary9790.org.au/test/test.asp>http://www.rotary9790.org.au/tes
t/test.asp
>
> From this SQL
>
> SELECT tMembers.MemberID, tMembers.PhoneBH, Len([PhoneAH]) AS Wdth
> FROM tMembers
> WHERE (((Len([PhoneAH]))=3))
> ORDER BY Len([PhoneAH]);
>
> Now I am even more confused! How does a field with ten digits show a
> width of "3"? The previous query, still shown on the page, does show
> "12" where there are 10 characters and two spaces.
>
> Many thanks,
>
> Robin Chapple