Removing multiple spaces

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

 

[John W. Vinson]

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.

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

Trim(" stuff ") will return "stuff".

 

[Lisa W.]

This doesn't seem to remove the 5 spaces before the first character.

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.

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

Trim(" stuff ") will return "stuff".

 

[John W. Vinson]

This doesn't seem to remove the 5 spaces before the first character.

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?

 

[Lisa W.]

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

 

[John W. Vinson]

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

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

 

[John Spencer]

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