Split first two words of string into new field

[Sharkiness]

Morning All,

I cannot find the answer to my question anywhere.

I have an address field named 'ADDRESS'. I want to run an update query so that the first two words of the address are split into a new field named 'AddressSplit'.

Therefore if the full address showed the following

'Ruby Cottage Edinburgh Scotland'

I would get 'Ruby Cottage' in new field.

Likewise if the address was '5 Aberdeen Way Glasgow Scotland'

I would get '5 Aberdeen' in the new field.

Would be grateful of any help and would prefer a query sequence rather than VBA code.

Thanks

Sharkiness

[ADezii]

Personally, I feel as though a simple Function, coupled with an Update Query will easily do the trick.

1. Function Definition:
   

   Public Function fSplitAddress(strAddress As String) As String
   Dim varSplit As Variant
   
   varSplit = Split(strAddress, " ")
   
   'Just in case, 1 word Address
   If UBound(varSplit) = 0 Then Exit Function
   
   fSplitAddress = varSplit(0) & " " & varSplit(1)
   End Function

2. UPDATE Query:
   

   UPDATE tblAddress SET tblAddress.AddressSplit = fSplitAddress([ADDRESS])
   WHERE tblAddress.ADDRESS Is Not Null;

3. Any questions, feel free to ask.

[NeoPa]

If you can work on the assumption that each word is separated by a single space, then you could use :

AddressSplit: Left([Address],InStr(InStr(1,[Address],' ')+1,[Address],' ')-1)

Unfortunately, most of the powerful functions (Split(), Join(), etc) can only be used within VBA as they require reference to arrays.

This is only the version to display the value. You should manage converting that to an update without assistance.

[NeoPa]

If you choose to use ADezii's approach (and there's no good reason not to unless performance is an issue across many hundreds of records) then you may want to adjust line #7 to :

If UBound(varSplit) = 0 Then
    fSplitAddress = strAddress
    Exit Function
End If

Currently, there appears to be no value set if there are no spaces found in the data. A simple oversight I expect.

[ADezii]

Hello NeoPa, won't the Code in Post# 3 fail for 2-Component Addressess such as 2937 Clifford, #47 Sunset, etc.?

[NeoPa]

Only if you ignore the assumption posted before it (which is supported by the example data given). This is a good point to bring up though, nevertheless.

If the assumption were that each word is separated by a single space, but optionally could include commas before some spaces (as ADezii suggests), then you could use :

AddressSplit: Replace(Left([Address],InStr(InStr(1,[Address],' ')+1,[Address],' ')-1),',','')

That is certainly a more standard format for addresses, I must admit.

A further point should be borne in mind. Such SQL will work from within Access, but such embedded function calls may cause trouble when accessing this query from outside of Access.

[NeoPa]

ADezii:
Hello NeoPa, won't the Code in Post# 3 fail for 2-Component Addressess such as 2937 Clifford, #47 Sunset, etc.?

I completely misunderstood your question. You mean for "2937 Clifford", "#47 Sunset", etc.

The answer is Yes. It does (fail). Let me see if I can find a more successful set of SQL for this, which handles the good point ADezii raised.

PS. Not only for two word data either. It also fails for a single word (It's quite cr*ppy really when I think about it :-D).

[NeoPa]

It's getting a little clumsy now. That is sometimes the nature of working in SQL rather than VBA. If you're determined that it must be done outside of VBA though, the following should work :

AddressSplit: IIf((Len([Address])-Len(Replace([Address],' ','')))>1,[Address],Replace(Left([Address],InStr(InStr(1,[Address],' ')+1,[Address],' ')-1),',',''))

Any comments are, as always, welcomed. I'd rather know if it's wrong than have everyone see I do cr*ppy answers ;-)

Thanks are due to ADezii for spotting what I should have spotted before posting.

[ADezii]

I guess that we are lucky enough to occasionally spot each other! (LOL).

[NeoPa]

It seems it's easier to spot each other's mistakes than our own eh, my friend?

I hate to post anything with mistakes in, but if I do, at least you spot them for me :-D

[Sharkiness]

Thanks guys, sorry for delay, only getting round to working on again now. Will get back to you if there are any problems. Although i doubt there will be

[NeoPa]

No worries :-)

Let us know if you have any problems.

[Sharkiness]

Not sure if you will know answer but could you take a look at this thread for me:

http://bytes.com/topic/access/answers/908837-can-i-export-form-subform-fields-excel-cells-template

Thanks

[NeoPa]

I looked, but it's not something I do. I export datasets to Excel, but never data formatted by an object such as a form or report. I could never understand why anyone would want to myself, but it seems many do.