Removing partial character strings

  • Thread starter Thread starter LSSR
  • Start date Start date
L

LSSR

I am using XP and Access 2003. I have 35,000 records in the table, of which
4,500 in text field address2 has characters such as “ # 101†(e.g., 123 Main
St. # 101; 456 Elm Ave. # A).

Starting from, and including the space that is on the left of the #, I need
to remove everything to the right (e.g., it would become 123 Main St.; 456
Elm Av.). Is there a way to automate this; much like you would use a
formula in MS Excel? I don’t know VBA. Thank you.
 
If you want to use just the portion to the left of the " #" characters, you
can base a query on your table, and enter a calculated field, thus:
ShortAddress2: Left([Address2],Instr(1,[Address2]," #")-1)

If you want to change the records in the table (and thereby lose the data to
the right of the # forever), run an update query:
UPDATE YourTableName SET [YourTableName].[Address2] =
Left([Address2],Instr(1,[Address2]," #")-1);

Make a backup copy of your datbase first ;-)

HTH,

Rob
 
Use an Update query to change the values in the field.
Use Instr() to locate the #.
Use Left() to get the characters to the left only.

I'm not sure if your example imples there could be multiple addresses in one
field in one record (separated by semicolons.) If so, you will need to write
a VBA function, using the Split() function to parse them into an array, and
use Instr() and Left() to process the array elements, and then concatenate
the array elements together as the return value of the function. You will
need some experience with VBA code to achieve this.
 
On Thu, 30 Oct 2008 22:06:01 -0700, LSSR

You're going to write your first VBA expression. It's not as hard as
you may think. The basic idea: Find the first #-sign and strip off
everything from one char to the left of it.
To find a char in a string, use InStr function.
To strip off, use Left$ in combination with Len.
I could give you the formula but isn't it more fun to discover it
yourself?
Start in the Immediate window to develop the expression. I might start
with something like:
? Left$("123 Main St. # 101", InStr("123 Main St. # 101", "#"))
Improve from there.
Then you write an Update query (AFTER you make a backup of your
database). Something like:
update SomeTable
set Address2 = Left$(Address2, InStr(Address2, "#"))
and let 'er rip!

-Tom.
Microsoft Access MVP
 
Back
Top