Solved

Splitting an address from a memo field

Posted on 2011-02-28
7
285 Views
Last Modified: 2012-06-21
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
Comment
Question by:Tra71
  • 4
  • 3
7 Comments
 
LVL 2

Expert Comment

by:jo_m
ID: 34995809

Hello  Tra

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

tx

jo
0
 

Author Comment

by:Tra71
ID: 34995846
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
 
LVL 2

Expert Comment

by:jo_m
ID: 34995870
Hi again  

 does the address have commas or spaces   eg

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

tx jo

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Tra71
ID: 34995899
Sorry split by carriage return.

Example:

18 Brown Street
Reading
Berks
0
 
LVL 2

Expert Comment

by:jo_m
ID: 34996045
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
 
LVL 2

Accepted Solution

by:
jo_m earned 250 total points
ID: 34996084

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
 

Author Closing Comment

by:Tra71
ID: 34996148
Thank you very much :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now