Separating text fields from the right hand side.

[drinniol]

I'm having trouble with separating a text field, starting from the right hand side.

The field is [Br1Suburb], and the data includes the suburb, the state, and the postcode separated by spaces. The problem is the suburb can have one (or more) spaces in it, for example;
Kingsley WA 6026
Alexander Heights WA 6064

I need to split the data as;

AddressPostCode: All text after the rightmost space (6026, 6064)
AddressState: All text between the second-rightmost space, and the rightmost space (WA)
AddressSuburb: All text left of the second-rightmost space (Kingsley, Alexander Heights)

I was able to do AddressPostCode easily enough:

Mid([Br1Suburb],InStrRev([Br1Suburb]," ")+1)

However, I can't for the life of me figure out how to do the others. All the solutions I have tried either give an error, or return the text between the first- and second-leftmost space.

[CJ_London]

remove the postcode and surplus space at end

replace([Br1Suburb],Mid([Br1Suburb],InStrRev([Br1Suburb]," "),""))

now apply your instrrev again

state=Mid(replace([Br1Suburb],Mid([Br1Suburb],InStrRev([Br1Suburb]," "))),InStrRev(replace([Br1Suburb],Mid([Br1Suburb],InStrRev([Br1Suburb]," ")))," ")+1)

or if you already have your postcode

postcode=Mid([Br1Suburb],InStrRev([Br1Suburb]," ")+1)

remove the postcode and surplus space at end
state=replace([Br1Suburb]," " & postcode,"")

now use the mid function again
state=Mid(state,InStrRev(state," ")+1)

or in one line
state=Mid(replace([Br1Suburb]," " & postcode,""),InStrRev(replace([Br1Suburb]," " & postcode,"")," ")+1)

the above is aircode, so may have got brackets in the wrong place but you should get the idea

[CJ_London]

there is another way using vba using the split function

function splitStr(s as string, r as string) as string
'this function assumes there are at least 3 elements in the s string, if not the function will need additional error handling
dim arr() as string
dim i as integer

arr=split(s," ")
select case r
    case "postcode"
        splitStr=arr(ubound(arr))
    case "State"
        splitStr=arr(ubound(arr)-1)
    case "Address"
        for i=1 to ubound(arr)-2
            splitStr=splitStr & arr(i) & " "
        next i
    case else
        msgbox "return string not recognised"
end select
splitstr=trim(splitstr)
end function

put the above function in a module (not a form module)

in a query you would call it like this

State: splitStr(Br1Suburb,"State")

[drinniol]

Hi Ajax,

Thanks for that, I think it's the right track. The State case works perfectly. However, the Address case is truncating the first word of the suburb, so it's either blank or returns only the second (and third, on some suburbs) word.

Example - A suburb named The Vines is only returning Vines. A suburb names Munno Para West returns Para West.

**EDIT***
Solved it;

Case "Address"
        For i = 0 To UBound(arr) - 2
            splitStr = splitStr & arr(i) & " "
        Next I

[CJ_London]

well caught!