how to remove a character and space from a field

[Dave]

Hi everyone,

I'm pretty new to access 2003 and I have been given some excel address
spreadsheets to import.
Trouble is the excel columns have been messed about with so when I import I
get an access table with fields like this.

Field [house no] = 12
Field [street] = Nantwich Road

Which is OK but I also get

Field [house no] = 13
Field [street] = a Nantwich Road

so I need to change the above to

Field [house no] = 13a
Field [street] = Nantwich Road

What kind of update query should I run to move the 'a' (or could be a 'b')
over and remove the remaining space.
Any help appreciated.

Cheers, David

 

[Ken Snell \(MVP\)]

You could do this with two update queries -- one to add the "a" to the
[house no] field, and the second to remove the "a " from the [street] field.

The trick, however, is to be able to tell the query exactly which records
"qualify" for this move.

Assuming that all values in [street] field that begin with "a " or "b " are
the ones to change, the two queries (in order of how they must be run) are
(NOTE: make a backup of your database file before you run any update query,
in case the results are not exactly what you'd wanted):

UPDATE YourTableName
SET [house no] = [house no] & Left([street], 1)
WHERE Left([street], 1) & " " IN
("a", "b");


UPDATE YourTableName
SET [street] = Mid([street], 3)
WHERE Left([street], 1) & " " IN
("a", "b");

 

[John Spencer]

If the letter to move is always one letter and is always followed by a
space.

UPDATE YourTableName
SET [House no] = [House no] & Left([Street,1)
, [Street] = Mid([Street],3)
WHERE [Street] Like "[a-z] *"




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Dave]

Thanks Ken and John for your help.
Thats brilliant just what I needed.
All the best, David