ACCESS FEED: [MS_AccessPros] Re: Dealing with spaces in a calculation

Jumat, 15 Februari 2013

[MS_AccessPros] Re: Dealing with spaces in a calculation

James

I think all you need to do is exclude all the rows if all fields are null. I only listed the first 2 fields. You can fill in the rest.

SET Registrations.AddressFull =
Trim(Nz([TEXT_RES_ADDRESS_NBR]+" ","") & Nz([TEXT_RES_ADDRESS_NBR_SUFFIX]+"
","") & Nz([CDE_STREET_DIR_PREFIX]+" ","") & Nz([TEXT_STREET_NAME]+" ","")
& Nz([DESC_STREET_TYPE]+" ","") & Nz([CDE_STREET_DIR_SUFFIX]+" ","") &
Nz([DESC_UNIT_TYPE]+" ","") & Nz([TEXT_RES_UNIT_NBR],""))
WHERE ([TEXT_RES_ADDRESS_NBR] Is Null
AND [TEXT_RES_ADDRESS_NBR_SUFFIX] Is Null
...) = False

Bill

--- In MS_Access_Professionals@yahoogroups.com, James McAlister wrote:
>
> Thanks, Bill! This indeed does keep the strings from being put into the
> field, but my query (below) gives an error because it's still trying to
> stuff 'em in there! I just dismiss the error (update not performed) and all
> is well, but it would be nice not to see the error at all by getting rid of
> the empty strings completely. But that may not be possible, at least with
> my limited expertise.
>
> Any further thoughts?
>
> Thanks!!
>
> UPDATE Registrations SET Registrations.AddressFull =
> Trim(Nz([TEXT_RES_ADDRESS_NBR]+" ","") & Nz([TEXT_RES_ADDRESS_NBR_SUFFIX]+"
> ","") & Nz([CDE_STREET_DIR_PREFIX]+" ","") & Nz([TEXT_STREET_NAME]+" ","")
> & Nz([DESC_STREET_TYPE]+" ","") & Nz([CDE_STREET_DIR_SUFFIX]+" ","") &
> Nz([DESC_UNIT_TYPE]+" ","") & Nz([TEXT_RES_UNIT_NBR],""));
>
>
> James
> www.james-mc.com
> Words To Live By
>
>
> On Fri, Feb 15, 2013 at 2:52 PM, Bill Mosca wrote:
>
> > **
> >
> >
> > James
> >
> > Access defaults all text fields to allow empty strings (which I hate!!).
> > First update your fields with an update query to replace those empty
> > strings. Make a copy of the table first just in case you lose data through
> > faulty syntax.
> >
> > UPDATE MyTable
> > SET MyField = Replace(MyField,"",Null),
> > MyOtherField = Replace(MyOtherField,"",Null)
> >
> > Once all the empty strings are gone open the table in design view and
> > change all the text fields to not allow zero-length (empty) strings.
> >
> > Then your criteria to not select nulls should work.
> >
> > Regards,
> > Bill Mosca, Founder - MS_Access_Professionals
> > http://www.thatlldoit.com
> > Microsoft Office Access MVP
> >
> > https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> > My nothing-to-do-with-Access blog
> > http://wrmosca.wordpress.com
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, James McAlister wrote:
> > >
> > > I'm using the following calculation to create a full address from eight
> > > component fields and populate an AddressFull field in a table:
> > >
> > > Trim(Nz([TEXT_RES_ADDRESS_NBR]+" ","") &
> > > Nz([TEXT_RES_ADDRESS_NBR_SUFFIX]+" ","") &
> > > Nz([CDE_STREET_DIR_PREFIX]+" ","") &
> > > Nz([TEXT_STREET_NAME]+" ","") &
> > > Nz([DESC_STREET_TYPE]+" ","") &
> > > Nz([CDE_STREET_DIR_SUFFIX]+" ","") &
> > > Nz([DESC_UNIT_TYPE]+" ","") &
> > > Nz([TEXT_RES_UNIT_NBR],""))
> > >
> > > This appears to work (sort of), as the full address is calculated without
> > > any extraneous spaces where one or more of the component fields is NULL.
> > It
> > > does appear, however, to leave an empty string in AddressFull if all
> > eight
> > > component fields are NULL. So when I query for NULL, no records are
> > > returned. But if I query for "", " ", " ", etc., I do find the records
> > > where all eight fields are empty and no full address is created.
> > >
> > > How can I modify the calculation so that I end up with a NULL (and not a
> > > string) when all eight of the component fields are NULL.?
> > >
> > > Access 2010.
> > >
> > > Thanks!
> > >
> > > James
> > > www.james-mc.com
> > > Words To Live By
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>