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"

Many Thanks Graham,

That was careless and shows my limitations. I copied the first query
and changed only the one reference. Now the results make sense.

How do I change the table design to disallow ZLS fields?

Regards,

Robin Chapple

At 19/09/2011 08:48 AM, you wrote:
>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
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>