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/test/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
At 19/09/2011 07:29 AM, you wrote:
>Thanks for your continuing support,
>
>The data consists of records inherited from many sources up to 30
>years old, some in different types of database some in spreadsheets.
>I have identified 840 records with the query:
>
>SELECT tMembers.MemberID, tMembers.PhoneAH, Len([PhoneAH]) AS Wdth
>FROM tMembers
>WHERE (((Len([PhoneAH]))=3))
>ORDER BY Len([PhoneAH]);
>
>I have found this reference:
>
><http://office.microsoft.com/en-us/access-help/update-data-by-using-a-query-HA010076527.aspx>http://office.microsoft.com/en-us/access-help/update-data-by-using-a-query-HA010076527.aspx
>
>
>
>but there is no mention of how to update to 'null'. There are many
>references for updating a 'null' value to a data field.
>
>Your advice will be appreciated.
>
>Many thanks,
>
>Robin Chapple
>
>At 19/09/2011 02:15 AM, you wrote:
>
> >Where is your data coming from? It should really be cleaned up at
> >the source. If you expect either a good Phone or nothing, you could
> >run an update query to replace all PhoneAH with null where the len()
> >of PHoneAH <=3.
> >
> >Duane Hookom
> >MS Access MVP
> >
> >
> >
> >
> >To: MS_Access_Professionals@yahoogroups.com
> >From: robinski@mymail.net.au
> >Date: Sun, 18 Sep 2011 17:18:21 +1000
> >Subject: RE: [MS_AccessPros] Confusion with use of "Is Null" and :Nz"
> >
> >
> >
> >
> >
> >
> >
> >Thanks Douane,
> >
> >We have a result which I do not understand.
> >
> >Your 'Result' below is "Result2" in the image at:
> >
> >http://www.rotary9790.org.au/test/test.asp
> >
> >The 'Wdth' result which shows "3" I have proven to be three unseen
> >characters. I can backspace them out and the result is a nil entry.
> >
> >How do I lose those with a query?
> >
> >Many thanks,
> >
> >Robin Chapple
> >
> >At 18/09/2011 01:08 PM, you wrote:
> >
> > >Add this column:
> > >Wdth: Len(PhoneAH)
> > >You can try
> > >Result:
> IIf(Len(Trim([PhoneAH]))=0,"Unknown",Format([PhoneAH],"@;""ZLS"""))
> > >
> > >Duane Hookom
> > >MS Access MVP
> > >
> > >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.o
> > rg.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
> > >
> > >
> > >
> >
> >
> >
> >
> >
> >
> >[Non-text portions of this message have been removed]
> >
> >
> >
> >------------------------------------
> >
> >Yahoo! Groups Links
> >
> >
> >
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>
Tidak ada komentar:
Posting Komentar