• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

Splitting an address from a memo field

Hi,

Easy question, my mind's gone blank.  I have a memo field for an address and I need to produce a report with splitting the address to show address, excluding the town and postcode and then a new column for the town.

Thanks
0
Tra71
Asked:
Tra71
  • 4
  • 3
1 Solution
 
jo_mCommented:

Hello  Tra

 could you post a quick sample,   this will make sure  we can help quickly

tx

jo
0
 
Tra71Author Commented:
Thanks Jo,

It's an access select query, so selecting the address, I want one column to show the address excluding the town and county and then in a second column, I want to show the town.

Thanks
0
 
jo_mCommented:
Hi again  

 does the address have commas or spaces   eg

 8  Brown St,   Reading,  Berks
 15 Marshall Road  Reading  Berksc  

tx jo

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Tra71Author Commented:
Sorry split by carriage return.

Example:

18 Brown Street
Reading
Berks
0
 
jo_mCommented:
hello

in a query copy the line below into a column
I called the memo field address,  replace this with the name of your memo field

ST: Mid([address],1,InStr([address],Chr$(13) & Chr$(10)))

this will give you the street name.

jo
0
 
jo_mCommented:

Sorry missed out the next part of the town.


To get the town in another column you need to get the position of the 2nd CRLF:

StreetPos: (InStr([address],(Chr$(13) & Chr$(10))))

 then using this position  you can get the town itself.
Town: Mid([address],[StreetPos]+2)

place these in the columns of a query,  this may go some way to resolving your  issue.

tx jo
0
 
Tra71Author Commented:
Thank you very much :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now