By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,761 Members | 1,062 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,761 IT Pros & Developers. It's quick & easy.

Spaces (Many Questions answered but none for me) :(

P: 3
First of all I should probably point out that my programming knowledge is NOT extensive and I'm not specifically well versed in the use of databases. For now I know what I need to know and I do my best to teach myself anything new that may be required. That being said, I am at present, beating my head against a wall with the current problem I have.

Just so it doesn't seem like I haven't, I have done some searches for my particular problem however to no avail. I was hopeful that the following thread would fix my issue however my version of Access, 2002, doesn't seem to work quite the same.
Other Thread

So without any further adieu here's my issue (that rhymed btw). I'm working with a Datasheet and one of the columns has data strings of varying size generally 7 to 8 characters long made up of both letters and numbers. Some of these strings have been added in with blank spaces at the end (example: BOBJ1234" ", the quotation marks aren't actually in the Datasheet they are just there to emphasize the space). What I want to do is to remove all the extra spaces without having to go through and one by one delete each space.

Any help that you can provide would be much appreciated!
Jun 5 '07 #1
Share this Question
Share on Google+
8 Replies

Rabbit
Expert Mod 10K+
P: 12,441
Run an update query that uses the replace function.
Jun 5 '07 #2

P: 3
How would one go about doing that?
Jun 5 '07 #3

Rabbit
Expert Mod 10K+
P: 12,441
How would one go about doing that?
You could use the query builder and change it to an update query or you can do it by writing the SQL statement.
Jun 5 '07 #4

P: 3
Okay, here's what I came up with from what I understand of SQL.

UPDATE tblEPPBU SET UserID = '*'
WHERE UserID='* ';

Now as I see it this says it should be updating the datasheet entitled 'tblEPPBU and removing a following 'blank space' in the 'UserID' column (denoted by the '*') from any records currently with an ending 'blank space'.

However when I run it, it doesn't seem to work. Any thoughts as to where I went wrong?
Jun 5 '07 #5

Rabbit
Expert Mod 10K+
P: 12,441
Okay, here's what I came up with from what I understand of SQL.

UPDATE tblEPPBU SET UserID = '*'
WHERE UserID='* ';

Now as I see it this says it should be updating the datasheet entitled 'tblEPPBU and removing a following 'blank space' in the 'UserID' column (denoted by the '*') from any records currently with an ending 'blank space'.

However when I run it, it doesn't seem to work. Any thoughts as to where I went wrong?
Without the Like operator, it will not see the asterisk as a wild card. You need to use the Replace function.

Expand|Select|Wrap|Line Numbers
  1. UPDATE tblEPPBU
  2. SET UserID = Replace(UserID, " ", "");
This will remove all spaces from UserID.
Jun 5 '07 #6

FishVal
Expert 2.5K+
P: 2,653
Without the Like operator, it will not see the asterisk as a wild card. You need to use the Replace function.

Expand|Select|Wrap|Line Numbers
  1. UPDATE tblEPPBU
  2. SET UserID = Replace(UserID, " ", "");
This will remove all spaces from UserID.

Rabbit, did you've noticed that Devo Jones want to remove terminating spaces only. This is a really great idea to remove all unnecessary spaces, isn't it?

Devo, if you want to remove terminating spaces only you should better run the following
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblEPPBU
  2. SET UserID = RTrim(UserID);
Jun 5 '07 #7

Rabbit
Expert Mod 10K+
P: 12,441
Rabbit, did you've noticed that Devo Jones want to remove terminating spaces only. This is a really great idea to remove all unnecessary spaces, isn't it?

Devo, if you want to remove terminating spaces only you should better run the following
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblEPPBU
  2. SET UserID = RTrim(UserID);
I know, but i figure if there are any other spaces that don't need to be in there then they may as well use replace.
Jun 5 '07 #8

P: 2
thats great it has also helped me out loads with preprocessing my data

i am also interested in doing a similar thing to remove non alphanumeric characters

i am new to access and my background is in perl so forgive me for speaking a different language but (if you guys make sense of this) is there anything like the equivalent of

s/\W//g;

(subtitute all non word characters for the empty string)
Jun 21 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.