Removing spaces from the middle

[deejay_totoro]

Hello all,

I am having real difficulty with this.

I have a field with postcode data like this:

"AB12 12CD"

What I would like to do in my query is remove the middle space.

Could anyone help?

Many thanks!

dj_T

 

[maxmangion]

=Replace([fieldname]," ","")

 

[IanH]

As I recall, REPLACE is a SQL and VBA function that cannot be used directly in a query. There are a few ways you can use it though.

One simple option is to create your own function in a VBA module as below ...

Function UDF_Replace(Orig_String As String, Find_Str As String, Replacement_Str As String) As String

UDF_Replace = Replace(Orig_String, Find_Str, Replacement_Str)

End Function​

Then open a new query and to select the string (without spaces) and check it is working correctly, you would need syntax like this ...

SELECT UDF_Replace([Postcode]," ","") AS Updated_Postcode FROM TABLE_NAME;​

To update the original table you would use the following ...

UPDATE TABLE_NAME SET [Postcode] = UDF_Replace([Postcode]," ","");​

Hope this helps

Ian

 

[maxmangion]

i've tested it directly in a query and it worked flawlessly.

 

[IanH]

Perhaps you are using an more up to date version of Access than I - as a poor public sector worker, we only have Office 2000 :-(

This was one of those functions that certainly didn't work previously, so may only be flawless if used on a later version. On the other hand, the VBA code should work across versions.

Regards

Ian

 

[maxmangion]

i've tested it on Office XP, but on Access 2000 format, and i still got it to work flawlessly.

 

[deejay_totoro]

replacement

Well,

Thanks for the replies.

Actually on Access 2000 it works fine for me =Replace([fieldname]," ","")
.

I was more surprised that there wasn't a more "basic" function to do this. We have Trim etc... but nothing that will hide or "parse" spaces in the middle of a string. Very strange.

Replacing seems "destructive" (actually replacing a space with nothing) rather than simply not showing the space, which I thought may be more inline with RDBS thinking?

maybe i am completely up the wrong street!

thanks!

dj_T