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

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 <wrmosca@comcast.net> 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]



------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/