Help with parsing text

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

Guest

Hello,

I have a field which has the following structure:

A/B/C

I need "B" but it can be any length (for the sake of argument). thanks
 
In a query, if you are certain that A is separated from B and B from C using
the "/", you could use:

B:Mid([yourField], instr([yourField], "/")+1, instrrev([yourField], "/") -
instr([yourField], "/") - 1)

HTH
Dale
 
I put in the following and I get an error.

B: Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1)


Dale Fye said:
In a query, if you are certain that A is separated from B and B from C using
the "/", you could use:

B:Mid([yourField], instr([yourField], "/")+1, instrrev([yourField], "/") -
instr([yourField], "/") - 1)

HTH
Dale

scubadiver said:
Hello,

I have a field which has the following structure:

A/B/C

I need "B" but it can be any length (for the sake of argument). thanks
Click to expand...
Click to expand...
 
Worked fine in the immediate window. Post your entire SQL string and I'll
see what I can do. It would also be nice to know what error your get.

Dale
--
Email address is not valid.
Please reply to newsgroup only.


scubadiver said:
I put in the following and I get an error.

B: Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1)


Dale Fye said:
In a query, if you are certain that A is separated from B and B from C using
the "/", you could use:

B:Mid([yourField], instr([yourField], "/")+1, instrrev([yourField], "/") -
instr([yourField], "/") - 1)

HTH
Dale

scubadiver said:
Hello,

I have a field which has the following structure:

A/B/C

I need "B" but it can be any length (for the sake of argument). thanks
Click to expand...
Click to expand...
Click to expand...
 
SELECT Data.Ref,
Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1) AS B
FROM Data;





Dale Fye said:
Worked fine in the immediate window. Post your entire SQL string and I'll
see what I can do. It would also be nice to know what error your get.

Dale
--
Email address is not valid.
Please reply to newsgroup only.


scubadiver said:
I put in the following and I get an error.

B: Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1)


Dale Fye said:
In a query, if you are certain that A is separated from B and B from C using
the "/", you could use:

B:Mid([yourField], instr([yourField], "/")+1, instrrev([yourField], "/") -
instr([yourField], "/") - 1)

HTH
Dale


Hello,

I have a field which has the following structure:

A/B/C

I need "B" but it can be any length (for the sake of argument). thanks
Click to expand...
Click to expand...
Click to expand...
 
You didn't indicate what error you got, so I'll assume it is has to do with
there being a NULL value in your field. Try:

SELECT Data.Ref,
IIF(ISNULL([Ref]), "N/A",
Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1)) AS B
FROM Data;

Actually, now that I take a closer look, I see that you dropped the
INSTRREV( ) function at the end. Keeping in mind that these text functions
won't work with a NULL value, It should be:

SELECT Data.Ref,
IIF(ISNULL([Ref]),
"N/A",

Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStrRev([Ref],"/")-1)) AS B
FROM Data;

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


scubadiver said:
SELECT Data.Ref,
Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1) AS B
FROM Data;





Dale Fye said:
Worked fine in the immediate window. Post your entire SQL string and I'll
see what I can do. It would also be nice to know what error your get.

Dale
--
Email address is not valid.
Please reply to newsgroup only.


scubadiver said:
I put in the following and I get an error.

B: Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1)


:

In a query, if you are certain that A is separated from B and B from C using
the "/", you could use:

B:Mid([yourField], instr([yourField], "/")+1, instrrev([yourField], "/") -
instr([yourField], "/") - 1)

HTH
Dale


Hello,

I have a field which has the following structure:

A/B/C

I need "B" but it can be any length (for the sake of argument). thanks
Click to expand...
Click to expand...
Click to expand...
 
There are no null values, "InstrRev" is an undefined function and there is
an extra ")"




Dale Fye said:
You didn't indicate what error you got, so I'll assume it is has to do with
there being a NULL value in your field. Try:

SELECT Data.Ref,
IIF(ISNULL([Ref]), "N/A",
Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1)) AS B
FROM Data;

Actually, now that I take a closer look, I see that you dropped the
INSTRREV( ) function at the end. Keeping in mind that these text functions
won't work with a NULL value, It should be:

SELECT Data.Ref,
IIF(ISNULL([Ref]),
"N/A",

Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStrRev([Ref],"/")-1)) AS B
FROM Data;

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


scubadiver said:
SELECT Data.Ref,
Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1) AS B
FROM Data;





Dale Fye said:
Worked fine in the immediate window. Post your entire SQL string and I'll
see what I can do. It would also be nice to know what error your get.

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I put in the following and I get an error.

B: Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1)


:

In a query, if you are certain that A is separated from B and B from C using
the "/", you could use:

B:Mid([yourField], instr([yourField], "/")+1, instrrev([yourField], "/") -
instr([yourField], "/") - 1)

HTH
Dale


Hello,

I have a field which has the following structure:

A/B/C

I need "B" but it can be any length (for the sake of argument). thanks
Click to expand...
Click to expand...
Click to expand...
 
Check your references, the InstrRev( ) function has been around for quite a
while, I think it was first available in Access 97. It finds the first
occurance of a string or character, starting from the end of the string.

Sorry about this error, I reversed the Instrrev and Instr functions during
the computation of the third parameter (length of string) in the query. If
you are certain there are no NULL values in the [Ref] field, then try:

SELECT Data.Ref,

Mid([Ref],InStr([Ref],"/")+1,InStrRev([Ref],"/")-InStr([Ref],"/")-1) AS B
FROM Data;

Dale

--
Email address is not valid.
Please reply to newsgroup only.


scubadiver said:
There are no null values, "InstrRev" is an undefined function and there is
an extra ")"




Dale Fye said:
You didn't indicate what error you got, so I'll assume it is has to do with
there being a NULL value in your field. Try:

SELECT Data.Ref,
IIF(ISNULL([Ref]), "N/A",
Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1)) AS B
FROM Data;

Actually, now that I take a closer look, I see that you dropped the
INSTRREV( ) function at the end. Keeping in mind that these text functions
won't work with a NULL value, It should be:

SELECT Data.Ref,
IIF(ISNULL([Ref]),
"N/A",

Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStrRev([Ref],"/")-1)) AS B
FROM Data;

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


scubadiver said:
SELECT Data.Ref,
Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1) AS B
FROM Data;





:

Worked fine in the immediate window. Post your entire SQL string and I'll
see what I can do. It would also be nice to know what error your get.

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I put in the following and I get an error.

B: Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1)


:

In a query, if you are certain that A is separated from B and B from C using
the "/", you could use:

B:Mid([yourField], instr([yourField], "/")+1, instrrev([yourField], "/") -
instr([yourField], "/") - 1)

HTH
Dale


Hello,

I have a field which has the following structure:

A/B/C

I need "B" but it can be any length (for the sake of argument). thanks
Click to expand...
Click to expand...
Click to expand...
 
I found in another thread that InStrRev would be unrecognised in v2000. I
can use two separate expressions to get around it.




Dale Fye said:
Check your references, the InstrRev( ) function has been around for quite a
while, I think it was first available in Access 97. It finds the first
occurance of a string or character, starting from the end of the string.

Sorry about this error, I reversed the Instrrev and Instr functions during
the computation of the third parameter (length of string) in the query. If
you are certain there are no NULL values in the [Ref] field, then try:

SELECT Data.Ref,

Mid([Ref],InStr([Ref],"/")+1,InStrRev([Ref],"/")-InStr([Ref],"/")-1) AS B
FROM Data;

Dale

--
Email address is not valid.
Please reply to newsgroup only.


scubadiver said:
There are no null values, "InstrRev" is an undefined function and there is
an extra ")"




Dale Fye said:
You didn't indicate what error you got, so I'll assume it is has to do with
there being a NULL value in your field. Try:

SELECT Data.Ref,
IIF(ISNULL([Ref]), "N/A",
Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1)) AS B
FROM Data;

Actually, now that I take a closer look, I see that you dropped the
INSTRREV( ) function at the end. Keeping in mind that these text functions
won't work with a NULL value, It should be:

SELECT Data.Ref,
IIF(ISNULL([Ref]),
"N/A",

Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStrRev([Ref],"/")-1)) AS B
FROM Data;

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:


SELECT Data.Ref,
Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1) AS B
FROM Data;





:

Worked fine in the immediate window. Post your entire SQL string and I'll
see what I can do. It would also be nice to know what error your get.

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I put in the following and I get an error.

B: Mid([Ref],InStr([Ref],"/")+1,InStr([Ref],"/")-InStr([Ref],"/")-1)


:

In a query, if you are certain that A is separated from B and B from C using
the "/", you could use:

B:Mid([yourField], instr([yourField], "/")+1, instrrev([yourField], "/") -
instr([yourField], "/") - 1)

HTH
Dale


Hello,

I have a field which has the following structure:

A/B/C

I need "B" but it can be any length (for the sake of argument). thanks
Click to expand...
Click to expand...
Click to expand...
 
NO PROBLEMS, CONTACT ME...
--
PACALA JAN [ACCESS DEVELOPER]
SEND 10$ TO...
ACCOUNT:
SK31 1100 0000 0026 1674 0428
SwiftCode: TATRSKBX
Tatra banka as,Hodzovo nam.3, 811 06 Bratislava
(e-mail address removed)
SKYPE: PACALA.BA1
 
These newsgroups are for the FREE exchange of ideas, information, and
assistance. This is absolutely NOT the place to troll for business: such
postings are not welcome, and they make such a bad impression that it can
only hurt your reputation in the Access community.

If you want to post actual advice and assistance here in the newsgroups for
all to benefit from, or post questions of your own, then your participation
will be welcomed.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


NO PROBLEMS, CONTACT ME...
--
PACALA JAN [ACCESS DEVELOPER]
SEND 10$ TO...
ACCOUNT:
SK31 1100 0000 0026 1674 0428
SwiftCode: TATRSKBX
Tatra banka as,Hodzovo nam.3, 811 06 Bratislava
(e-mail address removed)
SKYPE: PACALA.BA1


scubadiver said:
Hello,

I have a field which has the following structure:

A/B/C

I need "B" but it can be any length (for the sake of argument). thanks
Click to expand...
Click to expand...
 
Back
Top