I am sure I should know how to do this...but

[dynamictiger]

I want to chop an address into it's component parts. That is seperate off the numbers, from the name from the street type, so I end up with 35 Round Street as seperate strings.

Not all addresses are only numbers, so it seems to me the best way to do this is by testing for a space.

How do I do that?

 

[OldTom]

Instr will find one string within another (check access help for spec).

As you won't have completely uniform types of address data, which is very unlikely, I suggest you separate your string into substrings using the space separator first but watch out for "," and "-" also being used as separators.

Then you'll need to add some code to inspect those substrings in order to decide which belongs where.

I don't know what your data is like, but I've handled a lot of different address formats and these vary enormously from one country to another.

Watch out for Zip Codes being part of the address, they can move around quite a bit.

A few examples I know of that I would use for testing are

500 Quarry St
A-6213 Pertisau
RoyGlen,West Street
Links Place
124 Broadway

I'm sure you've got plenty of you own too

Hope this helps a little

Tom

 

[VBslammer]

VB contains a built-in parser called 'Split' you can use to do this. Here's an example:

Sub ParseAddress(ByVal strAddress As String)
' string array to hold results.
Dim strElements() As String
' counter for number of elements.
Dim i As Integer

' assign elements to array using 'space' as delimiter.
strElements = Split(strAddress, Chr(32))
' show the result set.
For i = 0 To UBound(strElements)
Debug.Print strElements(i)
Next i
End Sub

Use the procedure like this:

Call ParseAddress("35 Round Street&quot

The 3 strings will appear in the immediate window like:

35
Round
Street

Have fun,

VBSlammer

 

[OldTom]

VBSlammer had a good point there, but remember that your data might not always be delimitted by spaces or any other fixed character for that matter.
Tom