Selasa, 20 September 2011

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

 


Thanks Duane,

All the bad values were removed with the techniques that Graham described.

Your suggestion is a major improvement on my attempt and I have used
it and loaded it to the remote server. Now we are testing it 'live'

Many thanks,

Robin Chapple

At 21/09/2011 12:51 AM, you wrote:

>Robin,
>
>If you have changed your bad values to Null as Graham suggested, you
>might want to simplify your expression to something like what I
>suggested a long time ago. I believe someone posted recently
>regarding Null propogation.
>
> Phones: [PhoneAH]+" AH; "+" " & [PhoneBH]+" BH; "+" " & [Fax]+"
> Fax; "+" " & [Mobile]+" Mob "
>
>If you expect the values to all line up then you might switch Fx for
>Fax and Mo or pad some spaces after AH and BH.
>
>Duane Hookom
>MS Access MVP
>
>
>
>To: MS_Access_Professionals@yahoogroups.com
>From: robinski@mymail.net.au
>Date: Tue, 20 Sep 2011 22:24:28 +1000
>Subject: RE: [MS_AccessPros] Confusion with use of "Is Null" and :Nz"
>
>
>
>
>
>
>
>G'day Graham,
>
>The original data was inherited from spreadsheets and other databases
>and imported into Access.
>
>I have used your suggestions on all four fields and I have 100%
>success. Many thanks.
>
>Now I have made myself a small irritation with this concatenation
>which produces unbalanced spacing when one field is not used:
>
>Phones: Trim(IIf([PhoneAH] Is Null,"",([PhoneAH] & " AH; ")) & " " &
>IIf([PhoneBH] Is Null,"",([PhoneBH] & " BH; ")) & " " & IIf([Fax]
>Is Null,"",([Fax] & " Fax; ")) & " " & IIf([Mobile] Is
>Null,"",([Mobile] & " Mob ")))
>
>The illustration is here:
>
><http://www.rotary9790.org.au/test/test.asp>http://www.rotary9790.org.au/test/test.asp
>
>
>Is is possible to adjust the spaces?
>
>Cheers,
>
>Robin
>
>At 20/09/2011 03:44 PM, you wrote:
> >Hi Robin
> >
> >It looks like ZLS are already disallowed. This would be the error if 840
> >records contained one or more spaces and nothing else, and you tried to set
> >them to a ZLS returned by Trim(" "). Can you please check to verify this
> >is the case?
> >
> >If so, use these slightly modified queries:
> >
> >Query 1:
> >UPDATE tMembers SET [PhoneAH] = Trim([PhoneAH]) where
> >Len(Trim([PhoneAH]))>0;
> >
> >Query 2:
> >UPDATE tMembers SET [PhoneAH]=Null where Len(Trim([PhoneAH]))=0;
> >
> >It would be interesting to know how these fields came to be populated with
> >strings of spaces. Do you import this data from somewhere? If so then you
> >could use Trim() and check the result in your append/update query:
> >
> >IIf(Len(Trim([SourceField])()=0, Null, Trim([SourceField]))
> >
> >Cheers,
> >Graham
> >
> > > From: MS_Access_Professionals@yahoogroups.com
> >[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
> > > Sent: Tuesday, 20 September 2011 16:03
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: RE: [MS_AccessPros] Confusion with use of "Is Null" and :Nz"
> > >
> > >
> > >
> > > I decided to test the process on the copy that is still on my machine
> > > with this result:
> > >
> > > http://www.rotary9790.org.au/test/test.asp
> > >
> > > Should I proceed?
> > >
> > > Robin
> > >
> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~ Original message
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > >
> > > Thanks Graham,
> > >
> > > Our database is resident on a remote server and I need to make the
> > > changes when the keepers of the data are not working. Sunday was an
> > > opportunity which I missed. Tomorrow I have another 'window of
> > > opportunity' so please keep you fingers crossed for me.
> > >
> > > Cheers,
> > >
> > > Robin
> > >
> > > At 19/09/2011 11:42 AM, you wrote:
> > > >Hi Robin
> > > >
> > > >With the table in design view, select the field and, in the "Field
> > > >Properties" pane at the bottom of the screen, ensure that both
> "Required"
> > > >and "Allow Zero Length" are set to "No".
> > > >
> > > >Have you run the update queries as I suggested? If so, is your original
> > > >query working as required?
> > > >
> > > >Cheers,
> > > >Graham
> > > >
> > > > > From: MS_Access_Professionals@yahoogroups.com
> > > >[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin
> >Chapple
> > > > > Sent: Monday, 19 September 2011 11:54
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: 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
> >
> >
> >
> >------------------------------------
> >
> >Yahoo! Groups Links
> >
> >
> >
>
>
>
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar