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

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