Query remove spaces

[PG2015]

Hi I'm great at Excel but a newbie to ACCESS 2013.

Can anyone tell me how to remove all spaces from text (Postcode). I have tried a replace function but any postcodes without spaces show as an #Error

I would like it as a query not VBA.

Thanks for your help

 

[pr2-eugin]

Hello, Welcome to AWF

That is very bizarre, it should not throw any error. Please show the Query you are using.

 

[PG2015]

Hi Paul and thanks for the welcome.

I am using Expr1: [Field1]=Replace([field1]," "," ")

I have just revisited the problem and it appears the #Error displays when there is no postcode but at the moment the only return I am getting now is -1 ?

Looking to query Postcode field 1 and produce without spaces in text with no errors showing where there is no postcode.

Thanks for your help

 

[pr2-eugin]

In that case you could make use of the Nz() function.

Expr1: Replace(Nz([Field1], ""), " ", "")

 

[PG2015]

Brilliant! That got it first time - thanks very much for your help.

 

[pr2-eugin]

You are most welcome ! Good luck !

 

[gemma-the-husky]

it might be worth adding a ucase as well, in case any have lower case letters.

Expr1: ucase(Replace(Nz([Field1], ""), " ", ""))

 

[PG2015]

Thanks very much - it will come into use shortly.

Cheers for that!