update fields buy extracting

[kbreiss]

Not for sure if this is possible, but thought it's worth a try.
I have a "number" field that has numbers seperated by dashes. I would like each individual set of number/s between dashes be placed in its on field. My problem is some of the numbers have 2 numbers before the dash while some only have one number before the dash. If it were uniform I could either do a left, mid or right and it would work great. Here's an example of some of my data

35-22-11
02-10-18
9-8-1

Regardless of the how many numbers are present in a set I would like the first set beore the dash in one field, the second set of numbers in another and the third in another. Here's the query I used before I found out that not all the numbers are uniform in length.

UPDATE MASTER SET FIRST = nz(LEFT(NUMBERS,2),0), SECOND = nz(MID(NUMBERS,4,2),0), THIRD = nz(MID(NUMBERS,7,2),0;

Any help is appreciated.
Thanks,
Kacy
________
Paxil Side Effect

 

[Mile-O]

Use the InStr() function to determine the location of the dashes.

 

[kbreiss]

I'm using Access 97.......where do I find help on this function?

Thanks,
Kacy
________
BUY GLASS PIPES

 

[kbreiss]

Ok, I'm getting closer........ The query so far is working for the first and second numbes correctly. I'm not getting beyond that. Here's what I got so far

SELECT MASTER.DATE, MASTER.GAME, numbers, Nz(Left([Numbers],InStr([Numbers],"-")-1),0) AS First, Nz(Mid([Numbers],InStr([Numbers],"-")+1,InStr([Numbers],"-")-1),0) as Second
FROM MASTER;

Which puts the groups of numbers between dashes into seperate fields. I now need the third, fourth, fifth and sixth groups b/t dashes in seperate fields. It's tricky b/c I think I have to nest a couple of Instr() together and I'm lost on that.

##-#-##-##-#-##
numbers b/t dashes needs to be in seperate fields

Thanks,
Kacy
________
Montana Marijuana Dispensaries

 

[tadpole]

I know this maybe breaking the rules as this is an Access forum but I would export the number to excel and use the Data(menu), text to columns feature and use the dash- as the delimeter. Each number will be in a seperate column.

 

[kbreiss]

Yes, I believe that would work, but I would really like to keep it all in Access. I'm still trying to figure how to use the Mid() and inStr() function together. Here's a sample of the data that I would like each series of numbers between dashes in seperate fields.

##-##-#-##
#-##-##-#

I've got the Mid() and instr() function working for the numbers between the 1st and second dashes, but I'm stuck on the numbers between the second and third dashes.

Any help would be appreciated. I think I'm close w/ the example below on getting the numbers between the second and third dashes, but I'm still getting the numbers between the first and second dashes.

Select Mid(numbers, instr(instr([numbers], "-"),[numbers], "-"), instr([numbers], "-"))
From Master

Thanks,
Kacy
________
Suzuki Rgv250