Split function in query

[Brian]

..I tried using Split in a query to split a delimited number into individual
segments and got an Undefined function 'split' in expression error.

Is Split invalid in SQL? If so, is there a cognate function that can be used
within SQL? I get really tired of using search & replace to separate City &
State from City, State, ZIP concatenated fields when uysing Access in doing
database system integrations/upgrades.

 

['69 Camaro]

Hi, Brian.

Is Split invalid in SQL?
Yes.

is there a cognate function that can be used
within SQL?

You may create a user-defined function in VBA and call that from Jet SQL.
Please see Tom Wickerath's step-by-step example he posted recently on how to
create an update query that will split three names in one field into three
separate fields. You need only change his SplitName( ) function to
accommodate the comma and space, instead of just the space, CHR$(32), within
your string.

http://groups-beta.google.com/group/microsoft.public.access/msg/095849db18127ea4?hl=en&

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

[Brian]

Thanks for the tip. That was way too easy. Here is the final solution:

Function SplitString(Main As String, Delimiter As String, Segment As Integer)
SplitString = Split(Main, Delimiter)(Segment)
End Function

In query:

City: SplitString([FullAddress],",",0)
State: SplitString([FullAddress],",",1)
ZIP: SplitString([FullAddress],",",2)