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

Ah, yes, that looks good! Thanks!

James
www.james-mc.com
Words To Live By


On Fri, Feb 15, 2013 at 5:04 PM, Bill Mosca <wrmosca@comcast.net> wrote:

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


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