RE: Split function in query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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)



"'69 Camaro" wrote:

> 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 Ripley@xxxxxxxxxxxxxxx and scott@xxxxxxxxxxxxxxxxxx
>
> - - -
> 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" wrote:
>
> > .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.
.

Relevant Pages

  • Re: insert contents with , in it
    ... Yes, you are right, the ItemDesc is a memo field. ... ' or " as a text string delimiter. ... marks within the string delimited by " in order to insert just one. ... VBA variables and SQL - that will NOT work, since the ...
    (microsoft.public.access.forms)
  • Re: insert contents with , in it
    ... ' or " as a text string delimiter. ... marks within the string delimited by " in order to insert just one. ... VBA variables and SQL - that will NOT work, since the SQL ... INSERT INTO tProjectItems (ProjectID, ItemID, itemdesc, Unit, Qty, Item) ...
    (microsoft.public.access.forms)
  • Re: Behaviour of INSTR
    ... Given a string 'xxxx', and looking for occurances of the string 'xx', ... that once an occurance has been found, ... SQL> select instrfrom dual; ... parameter) I saw this problem when I had to use a delimiter like '---' ...
    (comp.databases.oracle.server)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)