Removing Spaces in Text Box - Frustrating !

  • Thread starter Thread starter kev100 via AccessMonster.com
  • Start date Start date
K

kev100 via AccessMonster.com

I've got an unbound text box where 4 fields are being combined to display on
1 line.

They are:

[FIRST] [MIDDLE] [LAST] [STATE]

The first 3 fields are 20 char. in size, the last is 2.

I'd like each to display all with 1 space between each, but only take up the
amount of space needed for the contents.

After doing several searchs in this forum....I've tried:

=[FIRST] & " " & [MIDDLE] & " " & [LAST] & " " & [STATE]

The info displays....but there is still much more of a gap than just 1 space
between each. I can't figure out why...since it seems to have worked for
everyone else.

ALSO........often there will not be a First or Middle name at all. It would
be great if that null could be ignored all together. Currenly, when one is
missing...there a big gap in its place...the existing info is not flush left.

Any advice appreciated.
Thanks
 
Hi

try this:

=Trim(
([FIRST] + " ")
& ([MIDDLE] + " ")
& ([LAST] + " ")
& [STATE]
)


Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)
 
Try this:

= Replace( Trim(NZ([FIRST],"")) & " " _
& Trim(NZ([MIDDLE],"")) & " " _
& Trim(NZ([LAST],"")) & " " _
& Trim(NZ([STATE],"")), " " " ")

Basically what it does is to replace all null fields with a Zero length
string, then remove all whitespace before an after each field, whilst
concatenating all of the fields with a Space between them. Finally it finds
all cases of two Space characters and replaces them with one Space
character, as would happen when one of the fields was Null.
 
You could try something like:
=([FIRST] + " ") & ([MIDDLE] + " ") & [LAST] & (" " + [STATE])

If any part of the expression with parentheses is null, the entire
expression evaluates to null if the plus sign is used as the concatenation
operator.
 
In addition to Crystal's comments, If you are getting additional spaces, it
suggests your data was not entered directly into Access. Perhaps this is a
linked table from SQL Server or pasted from some other source.

--
Duane Hookom
MS Access MVP

strive4peace said:
Hi

try this:

=Trim(
([FIRST] + " ")
& ([MIDDLE] + " ")
& ([LAST] + " ")
& [STATE]
)


Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

I've got an unbound text box where 4 fields are being combined to display
on
1 line.

They are:

[FIRST] [MIDDLE] [LAST] [STATE]

The first 3 fields are 20 char. in size, the last is 2.

I'd like each to display all with 1 space between each, but only take up
the
amount of space needed for the contents.

After doing several searchs in this forum....I've tried:

=[FIRST] & " " & [MIDDLE] & " " & [LAST] & " " & [STATE]

The info displays....but there is still much more of a gap than just 1
space
between each. I can't figure out why...since it seems to have worked for
everyone else.

ALSO........often there will not be a First or Middle name at all. It
would
be great if that null could be ignored all together. Currenly, when one
is
missing...there a big gap in its place...the existing info is not flush
left.

Any advice appreciated.
Thanks
Click to expand...
Click to expand...
 
Oops. For some reason I didn't see Crystal's reply when I posted. Her
reply includes Trim, so my reply is not only redundant, it is incomplete.

BruceM said:
You could try something like:
=([FIRST] + " ") & ([MIDDLE] + " ") & [LAST] & (" " + [STATE])

If any part of the expression with parentheses is null, the entire
expression evaluates to null if the plus sign is used as the concatenation
operator.

kev100 via AccessMonster.com said:
I've got an unbound text box where 4 fields are being combined to display
on
1 line.

They are:

[FIRST] [MIDDLE] [LAST] [STATE]

The first 3 fields are 20 char. in size, the last is 2.

I'd like each to display all with 1 space between each, but only take up
the
amount of space needed for the contents.

After doing several searchs in this forum....I've tried:

=[FIRST] & " " & [MIDDLE] & " " & [LAST] & " " & [STATE]

The info displays....but there is still much more of a gap than just 1
space
between each. I can't figure out why...since it seems to have worked for
everyone else.

ALSO........often there will not be a First or Middle name at all. It
would
be great if that null could be ignored all together. Currenly, when one
is
missing...there a big gap in its place...the existing info is not flush
left.

Any advice appreciated.
Thanks
Click to expand...
Click to expand...
 
Bruce -- you brought up a good point ..."If any part of the
expression with parentheses is null..."

thanks, seeing that in print made me realize another
potential problem ...

Kev -- will any of these fields ever have one or more spaces
as data? For instance, if you have values for FIRST and
LAST but spaces for MIDDLE, you will have trapped white
space -- you may want to try this if that is the case:

=Trim(
(IIF(Len(Trim(nz([FIRST]))) > 0, [FIRST] + " ",""))
& (IIF(Len(Trim(nz([MIDDLE]))) > 0, [MIDDLE] + " ",""))
& (IIF(Len(Trim(nz([LAST]))) > 0, [LAST] + " ",""))
& [STATE]
)

This also addresses Duane's comment, " If you are getting
additional spaces, it suggests your data was not entered
directly into Access. Perhaps this is a linked table from
SQL Server or pasted from some other source."

Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Oops. For some reason I didn't see Crystal's reply when I posted. Her
reply includes Trim, so my reply is not only redundant, it is incomplete.

You could try something like:
=([FIRST] + " ") & ([MIDDLE] + " ") & [LAST] & (" " + [STATE])

If any part of the expression with parentheses is null, the entire
expression evaluates to null if the plus sign is used as the concatenation
operator.

I've got an unbound text box where 4 fields are being combined to display
on
1 line.

They are:

[FIRST] [MIDDLE] [LAST] [STATE]

The first 3 fields are 20 char. in size, the last is 2.

I'd like each to display all with 1 space between each, but only take up
the
amount of space needed for the contents.

After doing several searchs in this forum....I've tried:

=[FIRST] & " " & [MIDDLE] & " " & [LAST] & " " & [STATE]

The info displays....but there is still much more of a gap than just 1
space
between each. I can't figure out why...since it seems to have worked for
everyone else.

ALSO........often there will not be a First or Middle name at all. It
would
be great if that null could be ignored all together. Currenly, when one
is
missing...there a big gap in its place...the existing info is not flush
left.

Any advice appreciated.
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Folks,

Thanks very much.....those worked ( I'm currently using the last one of
Crystal's) especially with ignoring the blank first name...when needed. There
is no longer a big gap when that is not present.

Those pesky spaces are still in there, though, when a field does have data.

This table is indeed a FoxPro 7.0 dbf file that is LINKED via ODBC....which I
am presuming is causing the issue.

Some of the data does have spaces between (some ocassionally have a needed
space...like a von in the last name... "von Brunner" etc)..and those spaces
ARE being retained.

However...for last name of Williams (8 characters), for example....the report
will still display:

"Williams TX"

rather than:

"Williams TX"

It may be that the Linked-via-ODBC issue simply causes spaces to be seen as
something other than spaces.

Thanks
 
you're welcome :)

sounds like you need one more touch...

=Trim(
(IIF(Len(Trim(nz([FIRST]))) > 0,
Trim([FIRST]) + " ",""))
& (IIF(Len(Trim(nz([MIDDLE]))) > 0,
Trim([MIDDLE]) + " ",""))
& (IIF(Len(Trim(nz([LAST]))) > 0,
Trim([LAST]) + " ",""))
& Trim([STATE])
)

Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)
 
Crystal!

Beautiful ! That works like a charm.....everything is nice a flush!

I even put in a divider character (;) along with the space to separate them
out ....and it will only show if there is actually data in that field.


Do you think that I could also use the ">" format item to Capitalize the
FIRST field....

Something like...

(IIF(Len(Trim(nz([FIRST]))) > 0, Trim(>[FIRST]) + " ","")) ?

Thanks again....this looks really cool.
 
you're welcome, Kev ;)

try using StrConv to propercase

(IIF(Len(Trim(nz([FIRST]))) > 0,
StrConv(Trim([FIRST]),vbProperCase) + " ",""))

StrConv("HELLO",vbProperCase) --> Hello


Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)
 
Thanks...very much...

Access for some reason keeps converting the vbProperCase into a variable
(field value)......it surrounds it with brackets and then prompt the user to
enter a value for [vbProperCase] whenever the form is run.

That result occurs when

(IIF(Len(Trim(nz([FIRST]))) > 0, StrConv(Trim([FIRST]),vbProperCase) + " ","")
)

is inserted into the existing coding (replacing any of the exising items that
apply to the FIRST field.

If I try using it by itself (just to test it in isolation)...Access displays
the message:

Extra ) in query expression...

I tried to determine where the extra one may be....and did a little editing...
but kept getting either the same....or the same with a ( .

Did I copy the code correctly?
 
Hi Kev,

you're welcome

The parenthesis are not balanced. The best way to balance
them is to use tabs and indent like this (to minimize
wrapping, I have used 1 space instead of 3 to seperate
indent levels):

( IIF
(Len
(Trim
(nz
([FIRST])
,"") 'added ,""
)
) > 0 'added parenthesis
,
StrConv
(Trim
([FIRST])
) 'added parenthesis
, vbProperCase
) + " "
,
""
)
' removed parenthesis

The art of balancing parenthesis is TRULY an art, but when
you start to break down your equations spacially, visual
inspection can find it much easier!

--- vbProperCase ---

try using its value, 3, instead

if the StrConv function won't work in a query, you can make
the equation a UDF (User Defined Function)... StrConv can be
executed in code but it is faster if you can imbed the equation.


Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)
 
Back
Top