MS Access data conversion - Splitting a text field value

Author

Splitting a text field value

Anna Peters via AccessMonster.com

2005-04-16, 8:23 pm

I have a table that is collecting website activity. Once a user clicks on a
page the URL is stored in a page_url (text field).

Here is an example of the value stored in the page_url field:

index.cfm?
section_id=24& geriatric_topic_id=4
&sub_section_id=34&page_id=49&tab=2

The URL is comprised of id's from other tables, each id is seperated by
"&". I want to be able to split the URL into values that I can link back to
their respective tables. For example, geriatric_topic is an PK in the topic
table and sub_section_id is a PK in the sub_section table.

Each topic has a sub section, while I am able to see which topics people
visit I can't get specifics about the sub_sections. So the only way I think
I can get this data is to split the URL and add the values into a seperate
table.

Any help would be much appreciated.

Thanks. :)

--
Message posted via http://www.accessmonster.com

Douglas J. Steele

2005-04-16, 8:23 pm

You can split the URL at the ? using the Left and Mid functions:

strBefore = Left(FullUrl, InStr(FullUrl, "?") - 1)
strAfter = Mid(FullUrl, InStr(FullUrl, "?") + 1)

Once you've done that, you can use the Split function to break the After
value into its component parts.

For the example you've given, if you do

Dim varParts As Variant

varParts = Split(strAfter, "&")

then varParts(0) will be section_id=24, varParts(1) will be
geriatric_topic_id, varParts(2) will be sub_section_id=34 and so on.

Hopefully that's enough to get you going.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Anna Peters via AccessMonster.com" <forum@nospam.AccessMonster.com> wrote
in message news:2c97994297a84b2
8b24f1f44591a4217@Ac
cessMonster.com...
>I have a table that is collecting website activity. Once a user clicks on a
> page the URL is stored in a page_url (text field).
>
> Here is an example of the value stored in the page_url field:
>
> index.cfm?
> section_id=24& geriatric_topic_id=4
&sub_section_id=34&page_id=49&tab=2
>
> The URL is comprised of id's from other tables, each id is seperated by
> "&". I want to be able to split the URL into values that I can link back
> to
> their respective tables. For example, geriatric_topic is an PK in the
> topic
> table and sub_section_id is a PK in the sub_section table.
>
> Each topic has a sub section, while I am able to see which topics people
> visit I can't get specifics about the sub_sections. So the only way I
> think
> I can get this data is to split the URL and add the values into a seperate
> table.
>
> Any help would be much appreciated.
>
> Thanks. :)
>
> --
> Message posted via http://www.accessmonster.com

Anna Peters via AccessMonster.com

2005-04-16, 8:23 pm

Hi Douglas,
Thank you for your reply. How can I implement this code, should I use an
UPDATE query?? If so can you please let me know how. I am sorry if this is
a dumb question. I am not new to access but I've never encountered a
problem like this.

--
Message posted via http://www.accessmonster.com

Douglas J. Steele

2005-04-16, 8:23 pm

Sorry, but without more details of what you're trying to do, I can't really
answer.

I'm not sure it'll be possible to do it strictly through queries. You're
doubtlessly going to need to use some VBA.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Anna Peters via AccessMonster.com" <forum@AccessMonster.com> wrote in
message news:928ac40a2926438
580310494b22e9af8@Ac
cessMonster.com...
> Hi Douglas,
> Thank you for your reply. How can I implement this code, should I use an
> UPDATE query?? If so can you please let me know how. I am sorry if this is
> a dumb question. I am not new to access but I've never encountered a
> problem like this.
>
> --
> Message posted via http://www.accessmonster.com

Anna Peters via AccessMonster.com

2005-04-17, 1:23 pm

Thanks for your help. I found another way to accomplish what I needed. I
don't think it was the most elegant solution but it isolated the
"need_help_stat_id" information.

Here is what I did, using the following URL as an example:

page_url
/index.cfm?
section_id=23&need_help_stat_id=2& geriatric_topic_id=3
&sub_section_id=30&page_id=38&tab=1

I used the following code to trim the url, I was left with
need_help_stat_id=2:

UPDATE stat_keywords SET need_help_stat_id = Left(page_url,InStr(
page_url,"?
")+33);

Then I used the following to to isolate the number:

UPDATE stat_keywords SET need_help_stat_id = Right([need_help_sta
t_id],1);

Then I changed the field type from text to a number and made my links back
to the need_help_stat table.

I'm not really clear how the trimming works I just tried different values
until I was able to isolate the information I needed.

Do you know of a webpage that has information on how the Right and Left
functions work???

Thanks for your help.

:)

--
Message posted via http://www.accessmonster.com

Douglas J. Steele

2005-04-17, 8:24 pm

The Help file does a pretty good job of explaining Left and Right as far as
I'm concerned.

Left returns a string containing a specified number of characters from the
left side of a string.

The syntax is:

Left(string, length)

where string is a string expression from which the leftmost characters are
returned, and length is a numeric expression indicating how many characters
to return.

If string contains Null, Null is returned. If length is 0, a zero-length
string ("") is returned. If length is greater than or equal to the number of
characters in string, the entire string is returned.

Right returns a string containing a specified number of characters from the
right side of a string.

The syntax is:

Right(string, length)

where string is a string expression from which the leftmost characters are
returned, and length is a numeric expression indicating how many characters
to return.

If string contains Null, Null is returned. If length is 0, a zero-length
string ("") is returned. If length is greater than or equal to the number of
characters in string, the entire string is returned.

Since they're such simple functions, I doubt you'll find any more details
anywhere else.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Anna Peters via AccessMonster.com" <forum@AccessMonster.com> wrote in
message news:9104415fb31a4a4
08ead85cebe464a74@Ac
cessMonster.com...
> Thanks for your help. I found another way to accomplish what I needed. I
> don't think it was the most elegant solution but it isolated the
> "need_help_stat_id" information.
>
> Here is what I did, using the following URL as an example:
>
> page_url
> /index.cfm?
> section_id=23&need_help_stat_id=2& geriatric_topic_id=3
&sub_section_id=30&page_id=38&tab=1
>
> I used the following code to trim the url, I was left with
> need_help_stat_id=2:

>
> UPDATE stat_keywords SET need_help_stat_id =
> Left(page_url,InStr(
page_url,"?
> ")+33);
>
> Then I used the following to to isolate the number:
>
> UPDATE stat_keywords SET need_help_stat_id = Right([need_help_sta
t_id],1);
>
> Then I changed the field type from text to a number and made my links back
> to the need_help_stat table.
>
> I'm not really clear how the trimming works I just tried different values
> until I was able to isolate the information I needed.
>
> Do you know of a webpage that has information on how the Right and Left
> functions work???
>
> Thanks for your help.
>
> :)
>
> --
> Message posted via http://www.accessmonster.com

Sponsored Links