Extracting Data based on number of characters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column that has a Sales module and a Payroll module. Now the Sales
module is 7 characters while the Payroll module is 8 characters, they are
seperated by a comma ie. TINA1234,UMA5678

The problem: The string TINA1234,UMA5678 could be reversed so that the
Payroll module is first while the Sales module is second ie.
UMA5678,TINA1234. I have around 100k rows that are intermixed.

I read through a lot of the questions here and found the following procedure
helpful.
LeftNumbers: Left([salesmod_nbr],InStr([salesmod_nbr],",")-1)
RightNumbers: Mid([salesmod_nbr],InStr([salesmod_nbr],",")+1)

The problem I'm having is I need all the Sales module in one column and all
the payroll in another column. Your help is very appreciated and thanks for
your time!
-Keith
 
If you wish to keep the data as it is in one column but split it when
extracting the data in select statements, you can achieve this with

SELECT
IIf(InStr(8,[salesmod_nbr],",")=8,Left([salesmod_nbr],InStr([salesmod_nbr],",")-1),Mid([salesmod_nbr],InStr([salesmod_nbr],",")+1))
AS salesNumber,
IIf(InStr(9,[salesmod_nbr],",")=9,Left([salesmod_nbr],InStr([salesmod_nbr],",")-1),Mid([salesmod_nbr],InStr([salesmod_nbr],",")+1)) AS payrollNumber
FROM YourTable;

If you wish to replace this existing column with two columns in the table,
then these could be populated with

UPDATE YourTable
SET salesNumber =
IIf(InStr(8,[salesmod_nbr],",")=8,Left([salesmod_nbr],InStr([salesmod_nbr],",")-1),Mid([salesmod_nbr],InStr([salesmod_nbr],",")+1)),
payrollNumber =
IIf(InStr(9,[salesmod_nbr],",")=9,Left([salesmod_nbr],InStr([salesmod_nbr],",")-1),Mid([salesmod_nbr],InStr([salesmod_nbr],",")+1))

Hope This Helps
Gerald Stanley MCSD
 
Try making a query looking for the one's on the right first and then make one
for the one's on the left. Select the right 7 of the one's on the right and
the left seven of the one's on the left. To search for the one on the right
try puting this in the search
*,???????
and the ones on the left
???????,*
I would add a temporary field and update it with a mark of somekind for all the
one's each query returns ... then you can look for the one's that get no answer
.... Roy, (e-mail address removed)
 
Dear Keith:

Sales_nbr = IIF(SUBSTR(salesmod_nbr, 8, 1) = ",", LEFT(salesmod_nbr,
7), RIGHT(salesmod_nbr, 7))

Payroll_nbr = IIF(SUBSTR(salesmod_nbr, 9, 1) = ",", LEFT(salesmod_nbr,
8), RIGHT(salesmod_nbr, 8))

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hey Tom,
Thank you for replying. I tried to put in the expression you stated but I
get an error that states "Undefined function"Substr" in expression.
I tried to click on help but it comes up blank. Do I need to load something
into my library for the "SUBSTR" to work?



Tom Ellison said:
Dear Keith:

Sales_nbr = IIF(SUBSTR(salesmod_nbr, 8, 1) = ",", LEFT(salesmod_nbr,
7), RIGHT(salesmod_nbr, 7))

Payroll_nbr = IIF(SUBSTR(salesmod_nbr, 9, 1) = ",", LEFT(salesmod_nbr,
8), RIGHT(salesmod_nbr, 8))

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a column that has a Sales module and a Payroll module. Now the Sales
module is 7 characters while the Payroll module is 8 characters, they are
seperated by a comma ie. TINA1234,UMA5678

The problem: The string TINA1234,UMA5678 could be reversed so that the
Payroll module is first while the Sales module is second ie.
UMA5678,TINA1234. I have around 100k rows that are intermixed.

I read through a lot of the questions here and found the following procedure
helpful.
LeftNumbers: Left([salesmod_nbr],InStr([salesmod_nbr],",")-1)
RightNumbers: Mid([salesmod_nbr],InStr([salesmod_nbr],",")+1)

The problem I'm having is I need all the Sales module in one column and all
the payroll in another column. Your help is very appreciated and thanks for
your time!
-Keith
Click to expand...
Click to expand...
 
Gerald,
Wow, thank you so much! It works perfect! Have a great weekend! You just
made mine!
-Keith

Gerald Stanley said:
If you wish to keep the data as it is in one column but split it when
extracting the data in select statements, you can achieve this with

SELECT
IIf(InStr(8,[salesmod_nbr],",")=8,Left([salesmod_nbr],InStr([salesmod_nbr],",")-1),Mid([salesmod_nbr],InStr([salesmod_nbr],",")+1))
AS salesNumber,
IIf(InStr(9,[salesmod_nbr],",")=9,Left([salesmod_nbr],InStr([salesmod_nbr],",")-1),Mid([salesmod_nbr],InStr([salesmod_nbr],",")+1)) AS payrollNumber
FROM YourTable;

If you wish to replace this existing column with two columns in the table,
then these could be populated with

UPDATE YourTable
SET salesNumber =
IIf(InStr(8,[salesmod_nbr],",")=8,Left([salesmod_nbr],InStr([salesmod_nbr],",")-1),Mid([salesmod_nbr],InStr([salesmod_nbr],",")+1)),
payrollNumber =
IIf(InStr(9,[salesmod_nbr],",")=9,Left([salesmod_nbr],InStr([salesmod_nbr],",")-1),Mid([salesmod_nbr],InStr([salesmod_nbr],",")+1))

Hope This Helps
Gerald Stanley MCSD

Keith said:
I have a column that has a Sales module and a Payroll module. Now the Sales
module is 7 characters while the Payroll module is 8 characters, they are
seperated by a comma ie. TINA1234,UMA5678

The problem: The string TINA1234,UMA5678 could be reversed so that the
Payroll module is first while the Sales module is second ie.
UMA5678,TINA1234. I have around 100k rows that are intermixed.

I read through a lot of the questions here and found the following procedure
helpful.
LeftNumbers: Left([salesmod_nbr],InStr([salesmod_nbr],",")-1)
RightNumbers: Mid([salesmod_nbr],InStr([salesmod_nbr],",")+1)

The problem I'm having is I need all the Sales module in one column and all
the payroll in another column. Your help is very appreciated and thanks for
your time!
-Keith
Click to expand...
Click to expand...
 
SUBSTR is the wrong function for Jet. The function you need there
would be MID instead. Sorry. I work in other databases and got my
wires crossed. Other than that, this should work for you I expect.

With that change, this is pretty much what Gerald suggested. His
method searches for a comma and tests whether it is found at position
8 or 9. My suggestion extracts the character at position 8 or 9 and
tests whether it is a comma. No real difference.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Dear Keith:

Sales_nbr = IIF(SUBSTR(salesmod_nbr, 8, 1) = ",", LEFT(salesmod_nbr,
7), RIGHT(salesmod_nbr, 7))

Payroll_nbr = IIF(SUBSTR(salesmod_nbr, 9, 1) = ",", LEFT(salesmod_nbr,
8), RIGHT(salesmod_nbr, 8))

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a column that has a Sales module and a Payroll module. Now the Sales
module is 7 characters while the Payroll module is 8 characters, they are
seperated by a comma ie. TINA1234,UMA5678

The problem: The string TINA1234,UMA5678 could be reversed so that the
Payroll module is first while the Sales module is second ie.
UMA5678,TINA1234. I have around 100k rows that are intermixed.

I read through a lot of the questions here and found the following procedure
helpful.
LeftNumbers: Left([salesmod_nbr],InStr([salesmod_nbr],",")-1)
RightNumbers: Mid([salesmod_nbr],InStr([salesmod_nbr],",")+1)

The problem I'm having is I need all the Sales module in one column and all
the payroll in another column. Your help is very appreciated and thanks for
your time!
-Keith
Click to expand...
Click to expand...
 
Back
Top