Hi Duane
Yeah - it was me posting about Null propagation :)
Do you not need parentheses around the subexpressions that are possibly
Null? I would use this expression:
Phones: ([PhoneAH]+" AH; ") & ([PhoneBH]+" BH; ") & ([Fax]+" Fax; ") &
([Mobile]+" Mob")
But perhaps the + is executed first because of operator precedence? I've
just never tried it.
@Robin: I'm very glad you got it all working. Remember to use that query
expression I gave you to ensure that any future import is clean!
Best wishes,
Graham
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane
> Hookom
> Sent: Wednesday, 21 September 2011 02:52
> To: Access Professionals Yahoo Group
> Subject: RE: [MS_AccessPros] Confusion with use of "Is Null" and :Nz"
>
>
> 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.a
> u/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
>
>
>
Tidak ada komentar:
Posting Komentar