Date and Time from URL string

[Steve]

Hi,

I have a field containing URL's with Day, Month, Year, and Hour information.
I need to parse this out and populate a Date/Time field that can be sorted
as well as be used in a PivotTable or PivotChart.

I started to work on this in a query with a calculated field using text
functions like Mid, Len, Left and Right but things started getting
complicated so I thought I should ask if anyone has a better solution.

So basically, pull the date-time information out of the URL and store it in
another Date/Time field as values that can be sorted or used in PivotTables
or PivotCharts.

Sample URL's are below:
http://www.kexp.org/playlist/old_playlist.asp?submitted=true&day=1&month=1&year=2009&hour=10 http://www.kexp.org/playlist/old_playlist.asp?submitted=true&day=1&month=1&year=2009&hour=11 http://www.kexp.org/playlist/old_playlist.asp?submitted=true&day=1&month=1&year=2009&hour=12 http://www.kexp.org/playlist/old_playlist.asp?submitted=true&day=1&month=1&year=2009&hour=13When parsed a seperate field would display these Date/Time's January 1, 2009 10:00 January 1, 2009 11:00 January 1, 2009 12:00 January 1, 2009 13:00I am working with Access 2002 SP3 on Windows XP Pro SP3Thanks for all your help, Steve.

 

[John Spencer]

Use the Split function and the mid function to get the parts

Use Mid to truncate the string starting with the &Day

Mid(URLString,InStr(1,URLString,"&day")+1

Now use the split function to break that up based on the & characters

Then check the various elements of the array to determine what what each
section is and then use mid on each array element to get the values.

You can use the DateSerial and TimeSerial functions to convert the value
into a valid dateTime data type.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[steverossiter]

Use the Split function and the mid function to get the parts

John, thank you. I could have spent a week finding my way to these
functions and logic. Steve.