Removing multiple spaces

  • Thread starter Thread starter Lisa W.
  • Start date Start date
L

Lisa W.

Is it possible to remove multiple spaces? For example, I have data imported
into a table that presents 5 spaces before the first character. I want to
create a query which removes those 5 spaces.

Thanks in Advance,
Lisa W.
 
Is it possible to remove multiple spaces? For example, I have data imported
into a table that presents 5 spaces before the first character. I want to
create a query which removes those 5 spaces.

Thanks in Advance,
Lisa W.
Click to expand...

Trim([fieldname]) will remove any number of leading or trailing blanks:

Trim(" stuff ") will return "stuff".
 
This doesn't seem to remove the 5 spaces before the first character.

John W. Vinson said:
Is it possible to remove multiple spaces? For example, I have data imported
into a table that presents 5 spaces before the first character. I want to
create a query which removes those 5 spaces.

Thanks in Advance,
Lisa W.
Click to expand...

Trim([fieldname]) will remove any number of leading or trailing blanks:

Trim(" stuff ") will return "stuff".
Click to expand...
 
This doesn't seem to remove the 5 spaces before the first character.
Click to expand...

Ummm???

From the Immediate window:

?"!" & Trim(" xyz ") & "!"
!xyz!

In what context are you using Trim? What is the actual content and length of
the field that you're trimming?
 
The content length will vary.

This is how the data appears in the field:
40066-d2001Pembr_GI_SP
6200240074-d2002Pompa_CB_EN

This is how I need it to appear in the field without the spaces:
40066-d2001Pembr_GI_SP
6200240074-d2002Pompa_CB_EN
 
The content length will vary.

This is how the data appears in the field:
40066-d2001Pembr_GI_SP
6200240074-d2002Pompa_CB_EN

This is how I need it to appear in the field without the spaces:
40066-d2001Pembr_GI_SP
6200240074-d2002Pompa_CB_EN
Click to expand...

?Trim(" 40066-d2001Pembr_GI_SP")
40066-d2001Pembr_GI_SP


Again: How are you using Trim? In what context? Do you want a Query returning
the trimmed value, or do you want to update the data in the table to
permanently discard the spaces, or what?
 
Another possibility is that there is a non-visible character at the beginning
of the string that is NOT a space character.

If you use
Asc([YourFieldName]) in a query, does it return 32? If the first character
is a space it will return 32. If it returns something else, post back with
the result.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top