Query remove spaces (1 Viewer)

PG2015

Registered User.
Local time
Today, 21:14
Joined
Feb 16, 2015
Messages
21
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

Super Moderator
Local time
Today, 21:14
Joined
Nov 30, 2011
Messages
8,494
Hello, Welcome to AWF :)

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

PG2015

Registered User.
Local time
Today, 21:14
Joined
Feb 16, 2015
Messages
21
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

Super Moderator
Local time
Today, 21:14
Joined
Nov 30, 2011
Messages
8,494
In that case you could make use of the Nz() function.
Code:
Expr1: Replace(Nz([Field1], ""), " ", "")
 

PG2015

Registered User.
Local time
Today, 21:14
Joined
Feb 16, 2015
Messages
21
Brilliant! That got it first time - thanks very much for your help. :)
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:14
Joined
Sep 12, 2006
Messages
15,614
it might be worth adding a ucase as well, in case any have lower case letters.

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

PG2015

Registered User.
Local time
Today, 21:14
Joined
Feb 16, 2015
Messages
21
Thanks very much - it will come into use shortly.

Cheers for that!
 

Users who are viewing this thread

Top Bottom