I've attached a sample database to attempt to explain better what I'm trying to do.
I'm trying to find the right combination of using the Mid and Instr function to extract each
series of numbers between dashes. With the sample query I am able to extact the first and seconds
series of numbers between dashes, but I can not get to go any farther than the second position.

I think for the third and beyond series it needs to be somthing like.........
Select numbers, numbers, Mid([numbers], Instr([numbers], Instr([numbers], "-"), "-"))
From Table1;
But I'm getting an "#error" on this query.


Hopefully this helps me better explain what I'm trying to do.

Thanks in advance.

Kacy

I used a custom function (Split_Striing) which works similar like the Split function in A2K.

Your Second field needs to be:
Second: Mid([Numbers],InStr([Numbers],"-")+1,InStr(Right([numbers],Len([numbers])-InStr([numbers],"-")),"-")-1)

(Try changing the length of the first to a size different from the second with your string and then try it with mine)

Which then would make your Third field be:
Third: Mid(Right([numbers],Len([numbers])-InStr([numbers],"-")),InStr(Right([numbers],Len([numbers])-InStr([numbers],"-")),"-")+1,InStr(Right(Right([numbers],Len([numbers])-InStr([numbers],"-")),Len(Right([numbers],Len([numbers])-InStr([numbers],"-")))-InStr(Right([numbers],Len([numbers])-InStr([numbers],"-")),"-")),"-")-1)

So, it is only going to get uglier from here to parse the rest of the string. So, a function like Nouba's to parse the string is the way to go.

This is great guys.........thank you very much. Have one more question.....if in those string of numbers and dashes I have a number in between [] can I just say for it to search for this in my query?

Here's my example....
99-569-78[00]

Thanks again.....exactly what I've been looking for!

Kacy

Do you mean substituting the '-' for '[' in the first part and '-' for ']' in the second? Uhh ... sure, but I don't think that is what you are asking ... is it?

That's what I'm trying to ask anyways. So for example, my number in the above post,

99-569-78[00]

I would like to have 99 in one field, 569 in one filed, 78 in one field and the 00 in one field.

I was hoping in the query giving to me I could substitute some how the "-" for a "[" to extract the number in brackets.

Hopefully this explains better what I'm trying to do.

Thanks,
Kacy

Are you using Nouba's solution? With her function, you pass the delimeter. So, you would have to use something other than [ and ]. It would have to be something consistant, like a + sign or something.

If you're not, re-write the Second string using the brackets.

Brackets: Mid([Numbers],InStr([Numbers],"[")+1,InStr(Right([numbers],Len([numbers])-InStr([numbers],"[")),"]")-1)

with the Replace function (I put one in the db) you can substitute the pair of brackets before giving the result to fctSplit as an argument.

Thanks guys.......on Nouba's db example I keep getting a compile error and can't figure out why. Any ideas?


Kacy

You could copy the code over to your own db and use the functions like in the query which you could hopefully see. What happens on manually compiling the db?

I'm still getting a compile error. I can live without this part however. I don't want you to have to spend anymore time on this. Thank you very much for your help.

Kacy

On the line:
Public Function Replace(ByRef Text As String, _
ByRef sOld As String, ByRef sNew As String, _
Optional ByVal Start As Long = 1, _
Optional ByVal Count As Long = 2147483647, _
Optional ByVal Compare As _
VbCompareMethod = vbBinaryCompare) As String

I get:

Compile Error:
Automation type not supported in Visual Basic

I developed the the sample with Access XP and have no chance to test it with A97 at home.

Can you check, if there are any missing references in the sample. What happens after removing the MISSING selections and recompling the db.

You know, that was the first thing I checked, but it showed nothing MISSING.

Beats me ...