Parse a field and translate

[drichter12]

I have a table in an Access database linked to an SQL table which I use to keep my Access database up to date. The piece I am having trouble with is a "business hours" field which is stored as a single 42 character string which needs to be translated into open and closed hours for every day of the week.

Here is an example of 1 string:
080180080180080180080180080180090170110160

This string translates as follows:
Monday Open - (080) = 8:00AM
Monday Close - (180) - 6:00PM
and so on through Sunday Close - (160) = 4:00PM

I have a query which presents all the fields in the table in a more readable format but am stuck at how to parse this one field into 14 fields which are more usable on the fly. I originally built a lookup table using every iteration of the string with the correct times linked to it but the problem with that is I come up with 452 unique strings currently in use and I am afraid that if someone changes one time entry which creates a new iteration then I will not have it accounted for and the record will no longer report for that location.

Any thoughts or suggestions would be greatly appreciated.

Thanks


Dale

 

[dhookom]

I'm not entirely sure what you mean by 452 unique strings or the changing stuff.

You can create a small user-defined function that accepts the string, day of the week, and open/close and returns the time:

Function GetTimeFromString(strTimes As String, intDay As Integer, strOC As String) As Date
    'strTimes is a string like "080180080180080180080180080180090170110160"
    'intDay is 1 - 7 for Sunday - Saturday
    'strOC is "Open" for Open and anything else for Close
    Dim str3Chars As String
    Dim intPosition As Integer
    intPosition = (intDay - 1) * 2 + 1 + IIf(strOC = "Open", 0, 1)
    str3Chars = Mid(strTimes, (intPosition - 1) * 3 + 1, 3)
    GetTimeFromString = Val(str3Chars) / 240
    
End Function

[tt][blue]
GetTimeFromString("080180080180080180080180080180090170110160",3,"Open") = 8:00:00 AM [/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

The soloution uses / assumes a standard bussiness week of Sunday through Saturday, while the question notes a Monday through Sunday week.



MichaelRed

 

[BigRed1212]

Are you wanting something like

select
Left(right(business_hours,42),3) as Mon_open,
Left(right(business_hours,39),3) as Mon_close,
Left(right(business_hours,36),3) as Tues_open,
Left(right(business_hours,33),3) as Tues_close, ....etc
from etc

Or are you wanting soemthing different?

 

[dhookom]

You are correct. Dale can use the function by passing in the "section number" rather than the day of the week. 1 would be the first section and would be Monday and 7 would be the last section and would be Sunday.

Duane
Hook'D on Access
MS Access MVP

 

[drichter12]

Thanks Duane, I will give it a shot over the weekend and see how it works. I'm a bit rusty but figure it shouldn't take too long to get back into the swing.


Dale