Splitting a text field value

[Anna Peters via AccessMonster.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.

 

[Douglas J. Steele]

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.

 

[Anna Peters via AccessMonster.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.

 

[Douglas J. Steele]

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.

 

[Anna Peters via AccessMonster.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_stat_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.

 

[Douglas J. Steele]

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.