Delete part of text string using code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a large number of address records containing the country (United
Kingdom)
I want to delete this from each record in which it appears.
Easy to do using Find..Replace but I want to automate using a query or
preferably using VBA code. I'm sure this is easy but I can't see how to do
it.

Also the addresses are currently displayed as an address line with each part
separated by commas. I want to convert to a block of text so it is correclty
laid out for a letter or envelope. I know the symbol to use to indicate a new
line which looks like a small square. However I don't know how to achieve
this using the keyboard.

Can anyone help please
 
ridders said:
i have a large number of address records containing the country (United
Kingdom)
I want to delete this from each record in which it appears.
Easy to do using Find..Replace but I want to automate using a query or
preferably using VBA code. I'm sure this is easy but I can't see how to do
it.

Also the addresses are currently displayed as an address line with each part
separated by commas. I want to convert to a block of text so it is correclty
laid out for a letter or envelope. I know the symbol to use to indicate a new
line which looks like a small square. However I don't know how to achieve
this using the keyboard.
Click to expand...


Check the Replace function in VBA Help.
Replace(address, ", UK", "")

The characters that Access uses for a new line is Chr(13) &
Chr(10). If your addresses have no other commas than the
separator between parts of the address:
Replace(address, ", ", Chr(13) & Chr(10))
 
You can use vbcrlf to duplicate the chr(10) & chr(13). If you are
typing in a box, use CTRL-Enter.


Chris Nebinger
 
Back
Top