left "_" & "-"

Here is some of my data that I want to unconcatonate... Need some help with
doing this as you can see the data length changes with each change of account.

1300_1 - CASH WA DISTRIBUTING
337100_1 - NORTH CENTER FOOD SERVICE
380009_1 - C & M FOOD
7258_1 - REINHART LACROSSE
216000_44 - SNOW FRESH APPETIZERS
3676_16 - GFS GREEN OAK DC
704125_10 - DOT FOODS

First numeric digits are a customer number, then the one or two digit
customer extension, and then the customer description....





--
GS

Try these ---
Customer: Left([YourField], InStr([YourField], "_")-1)
Extention: Left(Mid([YourField],InStr([YourField], "_")+1),
InStr([YourField], " ")-1)
Description: Right([YourField], Len([YourField]) - InStr([YourField], " ")+2)

--
KARL DEWEY
Build a little - Test a little


"Gary F Shelton" wrote:

Here is some of my data that I want to unconcatonate... Need some help with
doing this as you can see the data length changes with each change of account.

1300_1 - CASH WA DISTRIBUTING
337100_1 - NORTH CENTER FOOD SERVICE
380009_1 - C & M FOOD
7258_1 - REINHART LACROSSE
216000_44 - SNOW FRESH APPETIZERS
3676_16 - GFS GREEN OAK DC
704125_10 - DOT FOODS

First numeric digits are a customer number, then the one or two digit
customer extension, and then the customer description....





--
GS

Gary F Shelton wrote:
Here is some of my data that I want to unconcatonate... Need some
help with doing this as you can see the data length changes with each
change of account.

1300_1 - CASH WA DISTRIBUTING
337100_1 - NORTH CENTER FOOD SERVICE
380009_1 - C & M FOOD
7258_1 - REINHART LACROSSE
216000_44 - SNOW FRESH APPETIZERS
3676_16 - GFS GREEN OAK DC
704125_10 - DOT FOODS

First numeric digits are a customer number, then the one or two digit
customer extension, and then the customer description....

It would have helped if you had shown us the desired output for each row of
that sample data, but I think I can see that _ is used to separate the
customer number from the extension, and " - " is used to separate the
description.

It is possible to create a lengthy, difficult-to-maintain, expression that
uses InStr, Len, Mid, etc. to do this, but if I were doing it, I would write
a VBA function in a module, like this:

Public Function ParseCustString(sInput as string, _
iPortion as Integer) as String
dim aSplit as variant, s as string
Select Case iPortion
Case 1
'Customer Number
aSplit = Split(sInput,"_")
ParseCustString=aSplit(0)
Case 2
'Customer Extension
aSplit = Split(sInput,"_")
s=aSplit(1)
aSplit = Split(s," - ")
ParseCustString=aSplit(0)
Case 3
'Customer Description
aSplit = Split(sInput," - ")
ParseCustString=aSplit(1)
End Select
End Function

To use it:

Select ParseCustString([fieldname],1) As CustomerNumber,
ParseCustString([fieldname],2) As CustomerExtension,
ParseCustString([fieldname],3) As CustomerDescription
From tablename



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

On Tue, 8 Jul 2008 13:54:03 -0700, Gary F Shelton wrote:

Here is some of my data that I want to unconcatonate... Need some help with
doing this as you can see the data length changes with each change of account.

1300_1 - CASH WA DISTRIBUTING
337100_1 - NORTH CENTER FOOD SERVICE
380009_1 - C & M FOOD
7258_1 - REINHART LACROSSE
216000_44 - SNOW FRESH APPETIZERS
3676_16 - GFS GREEN OAK DC
704125_10 - DOT FOODS

First numeric digits are a customer number, then the one or two digit
customer extension, and then the customer description....

CustNumber:Val([FieldName])
Extension:Val(Mid([FieldName],Instr([FieldName],"_")+1))
Description:Mid([FieldName],InStr([FieldName],"-")+2)

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

Gary F Shelton wrote:

Here is some of my data that I want to unconcatonate... Need some help with
doing this as you can see the data length changes with each change of account.

1300_1 - CASH WA DISTRIBUTING
337100_1 - NORTH CENTER FOOD SERVICE
380009_1 - C & M FOOD
7258_1 - REINHART LACROSSE
216000_44 - SNOW FRESH APPETIZERS
3676_16 - GFS GREEN OAK DC
704125_10 - DOT FOODS

First numeric digits are a customer number, then the one or two digit
customer extension, and then the customer description....


Use Sub procedure with the InStr function (see VBA Help) to
first locate the _ then the " - ". You can then use the
Left, Righr and Mid functions to get the separate parts.

--
Marsh
MVP [MS Access]

Thank you everyone ... I got it to work with your suggestions...
--
GS


"Marshall Barton" wrote:

Gary F Shelton wrote:

Here is some of my data that I want to unconcatonate... Need some help with
doing this as you can see the data length changes with each change of account.

1300_1 - CASH WA DISTRIBUTING
337100_1 - NORTH CENTER FOOD SERVICE
380009_1 - C & M FOOD
7258_1 - REINHART LACROSSE
216000_44 - SNOW FRESH APPETIZERS
3676_16 - GFS GREEN OAK DC
704125_10 - DOT FOODS

First numeric digits are a customer number, then the one or two digit
customer extension, and then the customer description....


Use Sub procedure with the InStr function (see VBA Help) to
first locate the _ then the " - ". You can then use the
Left, Righr and Mid functions to get the separate parts.

--
Marsh
MVP [MS Access]