Remove a space from field data

[Garry]

Hi all

How can I remove a blank space from a field.

e.g.

A 123 ABC or 1 AB 123 etc, so as to get

A123 ABC and 1AB 123 whilst retaining the second blank space.

Thanks to anyone in advance, Garry

--

 

[Tom Ellison]

Dear Garry:

The steps are as follows:

1. Use Instr to locate the first space in the string. In your examples
this would be the value 2 in both cases. If it is always 2, you can skip
this step.

2. Using N to represent the value obtained (2 in both your examples)
extract the string portions left and right of that position. Use the Left
function to get the first, as in:

Left(YourString, N - 1)

and the Mid function to get everything to the right of that position:

Mid(YourString, N + 1)

3. Concatenate these two and you have the final product.

You can create a function to do this, but for the simplest cases all you
need is the above which can be placed entirely within your query.

Tom Ellison

 

[John Nurick]

If you're using a recent version of Access you can use the VBA Replace()
function in a query. Passing 1 as the fifth argument ensures that it
only replaces the first space, e.g.

Replace([MyField], " ", "", 1, 1)

 

[Tom Ellison]

Dear Garry:

What John says here is certainly a way to remove space(s) from your text.
However, in your examples, you removed the first space and left the second.
That is not what this one does. Anyway, that's why I did not pass on this
method to you. So, you can see for yourself which is what you want.

Also, what John couldn't have known is that you replied to me by email that
this had solved your problem. May I gently suggest that if you also reply
within the newsgroup, then others will see that your problem is solved and
not spend time, as John has so graciously done here, on a problem that is
closed?

Tom Ellison

If you're using a recent version of Access you can use the VBA Replace()
function in a query. Passing 1 as the fifth argument ensures that it
only replaces the first space, e.g.

Replace([MyField], " ", "", 1, 1)

Hi all

How can I remove a blank space from a field.

e.g.

A 123 ABC or 1 AB 123 etc, so as to get

A123 ABC and 1AB 123 whilst retaining the second blank space.

Thanks to anyone in advance, Garry

 

[John Nurick]

Hi Tom,

What John says here is certainly a way to remove space(s) from your text.
However, in your examples, you removed the first space and left the second.
That is not what this one does.

S = "123 456 789 ABC"
? Replace(S, " ", "", 1, 1)
123456 789 ABC

Am I missing something, or are you?

 

[Tom Ellison]

Me!

Thanks.

Tom Ellison

Hi Tom,



S = "123 456 789 ABC"
? Replace(S, " ", "", 1, 1)
123456 789 ABC

Am I missing something, or are you?