Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 02-16-2015, 03:26 AM   #1
PG2015
Newly Registered User
 
Join Date: Feb 2015
Posts: 21
Thanks: 13
Thanked 0 Times in 0 Posts
PG2015 is on a distinguished road
Smile Query remove spaces

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

PG2015 is offline   Reply With Quote
Old 02-16-2015, 03:31 AM   #2
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,065 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Query remove spaces

Hello, Welcome to AWF

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 02-16-2015, 03:48 AM   #3
PG2015
Newly Registered User
 
Join Date: Feb 2015
Posts: 21
Thanks: 13
Thanked 0 Times in 0 Posts
PG2015 is on a distinguished road
Question Re: Query remove spaces

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

PG2015 is offline   Reply With Quote
Old 02-16-2015, 03:53 AM   #4
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,065 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Query remove spaces

In that case you could make use of the Nz() function.
Code:
Expr1: Replace(Nz([Field1], ""), " ", "")
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 02-16-2015, 03:57 AM   #5
PG2015
Newly Registered User
 
Join Date: Feb 2015
Posts: 21
Thanks: 13
Thanked 0 Times in 0 Posts
PG2015 is on a distinguished road
Re: Query remove spaces

Brilliant! That got it first time - thanks very much for your help.
PG2015 is offline   Reply With Quote
Old 02-16-2015, 03:58 AM   #6
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,065 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Query remove spaces

You are most welcome ! Good luck !
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 02-16-2015, 04:44 AM   #7
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,766
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Query remove spaces

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

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

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 02-16-2015, 04:47 AM   #8
PG2015
Newly Registered User
 
Join Date: Feb 2015
Posts: 21
Thanks: 13
Thanked 0 Times in 0 Posts
PG2015 is on a distinguished road
Re: Query remove spaces

Thanks very much - it will come into use shortly.

Cheers for that!

PG2015 is offline   Reply With Quote
Reply

Tags
query , spaces , text

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Update query to remove spaces and export data Steviet811 Queries 1 11-20-2012 06:04 AM
Query to remove spaces between numbers failing optionone Queries 2 11-02-2012 02:51 AM
Query to remove unwanted spaces before and after after conversion hunoob Queries 1 03-14-2009 02:46 AM
Update query to remove spaces DeanRowe Queries 5 11-21-2008 01:45 PM
remove spaces ice-9 Queries 2 11-07-2005 12:40 AM




All times are GMT -8. The time now is 09:49 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World