Opposite of Concatenate???

[petrosky]

Hi,

I have nondelimited data in my table...

eg

FIELD1

Melbourne Vic 3000
Brisbane Qld 4000


Is there an easy way to "break" this data out.

Many thanks Peter. Remember- It's nice to be important,
but it's important to be nice

 

[SeeThru]

You can use the Instr() function, combines wuth the Chr() function to fund special characters - the line returns will be 10 or 13 - and then split each field into various variables using the Left() and Mid() test handlers.

I dont have a bit of code written at the moment, but it isnt too bad to do. You can even use the text functions in an update, append or make-table query.

 

[petermeachem]

It must be delimited somehow or you won't be able to split it using a programme. If you can form a set of rules as to how the data is made up, then you can split it.

It looks like townname space(s) stateabbreviation space(s) somenumber ? Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[raskew]

Peter-

You might want to consider a 'right to left' approach, which
will allow you to deal with the possibility that the city name
consists of more than one word (e.g. "West Palm Beach&quot.

This solution assumes that your state or territory abbreviation
never contains spaces:

Copy function xLastInStr() - shown below - to a module. Then,
from the debug window:

widget = "West Palm Beach FL 33401"
LastSpace = xLastInStr(Widget, " &quot
NextToLastSpace = xLastInStr(Left(Widget, LastSpace - 1)," &quot
lenState = LastSpace - NextToLastSpace - 1
City = Left(widget, NextToLastSpace -1)
State = mid(widget, NextToLastSpace + 1, lenState)
Zip = mid(widget, LastSpace + 1)

? City
West Palm Beach

? State
FL

? Zip
33401

*******************************************************************
' FUNCTION: xLastInStr()
'
' PURPOSE: Determine the position of the last character(s)
' (as specified by user) in a string.
'
' ARGUMENTS:
' tstr: The string to be tested
' twhat: The character to locate.
'
' RETURNS: An integer representing the last occurence or, if not found, 0.
'
' NOTES: To test: Type '? xLastInStr("The quick brown fox jumped over
' the lazy dog", "the&quot in the debug window.
' The function will return 33.

Function xLastInStr(ByVal tstr As String, twhat As String) As Integer
Dim I As Integer, n As Integer, tlen As Integer

n = 0
tlen = Len(twhat)
For I = Len(RTrim(tstr)) To 1 Step -1

If Mid(tstr, I, tlen) = twhat Then
n = I
Exit For
End If
Next I

xLastInStr = n

End Function

HTH, Bob

 

[petermeachem]

Have you seen instrrev. It's the same as instr but works from right to left. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[petrosky]

Hi all,

Many thanks for the assistance and great ideas.

Peter...I did a search on the instrrev function but could find nothing (Access 97 here)

Raskew, please forgive my ignorance but do I somehow need to add/declare these in your function to get it to work...

widget = "West Palm Beach FL 33401"
LastSpace = xLastInStr(Widget, " &quot
NextToLastSpace = xLastInStr(Left(Widget, LastSpace - 1)," &quot
lenState = LastSpace - NextToLastSpace - 1
City = Left(widget, NextToLastSpace -1)
State = mid(widget, NextToLastSpace + 1, lenState)
Zip = mid(widget, LastSpace + 1)

How can I apply this to either an Update query or a Make table query?

Regards,

Peter



Remember- It's nice to be important,
but it's important to be nice