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
Tra71Asked:
Who is Participating?
 
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
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
 
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
 
Tra71Author Commented:
Thank you very much :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.