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

Senin, 19 September 2011

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

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