Selasa, 20 September 2011

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
>
>
>

__._,_.___
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