Extract Data from a String

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

navyman2u - 19 Jul 2006 22:25 GMT

I have MS Access 2000 and I have an web address in which I am trying to
extract the text between the "=" and the "&" inside a table column. I don't
know if it best to do this in a Query or VBA.

1. Can this be done?
2. If so, which is the best method?
3. And, if using VBA then what do I do next.

I don't have much experience writing VBA so please be specific as possible.
Thanks.

Reply to this Message

Ofer Cohen - 19 Jul 2006 23:09 GMT

I'm not sure that what you mean, but try

Mid([FieldName],instr([FieldName],"=")+1,Instr([FieldName],"&")-instr([FieldName],"=")-1)

Signature

Good Luck
BS"D

> I have MS Access 2000 and I have an web address in which I am trying to
> extract the text between the "=" and the "&" inside a table column. I don't
[quoted text clipped - 6 lines]
> I don't have much experience writing VBA so please be specific as possible.
> Thanks.

Reply to this Message

navyman2u - 20 Jul 2006 14:44 GMT

Thanks for the help! It solved my dilemma. I appreciate the help!
Mr. Chase

> I'm not sure that what you mean, but try
>
[quoted text clipped - 10 lines]
> > I don't have much experience writing VBA so please be specific as possible.
> > Thanks.

Reply to this Message

Jamie Collins - 20 Jul 2006 16:07 GMT

> I have MS Access 2000 and I have an web address in which I am trying to
> extract the text between the "=" and the "&" inside a table column. I don't
> know if it best to do this in a Query or VBA.

If you have a have an auxiliary table (Sequence) of integers (seq), you
can create a cross join to your table (InputStrings) and parse the
values (input_string) in SQL:

SELECT T1.input_string, MID$(T1.input_string, S1.seq + 1, MIN(S2.seq -
S1.seq - 1)) AS input_string_parsed
FROM InputStrings AS T1, [Sequence] AS S1, [Sequence] AS S2
WHERE MID$(T1.input_string, S1.seq, 1) = '='
AND MID$(T1.input_string, S2.seq, 1) = '&'
AND S1.seq < S2.seq
AND S2.seq BETWEEN 2 AND LEN(T1.input_string)
GROUP BY T1.input_string, S1.seq;

Jamie.

--

Reply to this Message