vba function within Access query

[patrick beyries]

fair Warning - Admittedly I'm far from an access expert, but I'm not a
beginner either.

I want to split a value into multiple parts as delimited by a character
- all as part of a query. For example, one specific column in a table
contains values like "eggs/ham/potato" and "toast/fruit" and as I run a
select query against this table, I want to split the above values into
"eggs", "ham", "potato" (3 values)and "toast","fr uit" (2 values)

My research has led me to the VBA function Split() but I've been
unsuccessful in incorporating the function into a query.

Thank you for your time.

*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

Re: vba function within Access query

If this is Access 2002 or 2003, you should be able to parse the first
element from field [d] with:
Split([d],"/")(0)

Access 2000 might have problems doing that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"patrick beyries" <patrick.beyrie s@keynote.com> wrote in message
news:V19hg.54$y y2.13735@news.u swest.net...[color=blue]
> fair Warning - Admittedly I'm far from an access expert, but I'm not a
> beginner either.
>
> I want to split a value into multiple parts as delimited by a character
> - all as part of a query. For example, one specific column in a table
> contains values like "eggs/ham/potato" and "toast/fruit" and as I run a
> select query against this table, I want to split the above values into
> "eggs", "ham", "potato" (3 values)and "toast","fr uit" (2 values)
>
> My research has led me to the VBA function Split() but I've been
> unsuccessful in incorporating the function into a query.[/color]

[fredg]

Re: vba function within Access query

On Tue, 06 Jun 2006 06:13:09 GMT, patrick beyries wrote:
[color=blue]
> fair Warning - Admittedly I'm far from an access expert, but I'm not a
> beginner either.
>
> I want to split a value into multiple parts as delimited by a character
> - all as part of a query. For example, one specific column in a table
> contains values like "eggs/ham/potato" and "toast/fruit" and as I run a
> select query against this table, I want to split the above values into
> "eggs", "ham", "potato" (3 values)and "toast","fr uit" (2 values)
>
> My research has led me to the VBA function Split() but I've been
> unsuccessful in incorporating the function into a query.
>
> Thank you for your time.
>
> *** Sent via Developersdex http://www.developersdex.com ***[/color]

If you have Access 2000 or newer:
Copy and Paste the following into a Module:

Public Function ParseText(TextI n As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, "/", -1)
ParseText = var(X)

End Function
===========

Then add 3 new columns in the query grid:

FirstCol:ParseT ext([FieldNamel],0)
SecondCol:Parse Text([FieldNamel],1)
ThirdCol:ParseT ext([FieldNamel],2)

Change FieldName to what ever the actual field names is.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail