Distinguishing Weekends and Weekdays.

[hongbin81]

I have a Date Field, Time Field and a Rate Field in a table called tblLogs. There's this one problem I'm encountering and hopefully someone can help me.

The Rates of these Dates depends on the time range. Here's the code for the different rates for different time ranges.

Select Case Me![Time]

'PRIME B
Case #7:00:00 AM# To #8:59:59 AM#
If Duration = 15 Then
SpotRate = 33
ElseIf Duration = 30 Then
SpotRate = 55
ElseIf Duration = 45 Then
SpotRate = 77
ElseIf Duration = 60 Then
SpotRate = 93
Else
SpotRate = 0

End If

'REGULAR B
Case #9:00:00 AM# To #10:14:59 AM#
If Duration = 15 Then
SpotRate = 18
ElseIf Duration = 30 Then
SpotRate = 30
ElseIf Duration = 45 Then
SpotRate = 42
ElseIf Duration = 60 Then
SpotRate = 51
Else
SpotRate = 0

End If

'REGULAR A
Case #10:15:00 AM# To #10:49:59 AM#
If Duration = 15 Then
SpotRate = 24
ElseIf Duration = 30 Then
SpotRate = 40
ElseIf Duration = 45 Then
SpotRate = 56
ElseIf Duration = 60 Then
SpotRate = 68
Else
SpotRate = 0

End If

'OFF PEAK
Case #10:50:00 AM# To #11:24:59 AM#
If Duration = 15 Then
SpotRate = 9
ElseIf Duration = 30 Then
SpotRate = 15
ElseIf Duration = 45 Then
SpotRate = 21
ElseIf Duration = 60 Then
SpotRate = 25
Else
SpotRate = 0


End If

AND SO ON.....
But the problem is, this Rate is the same for every Date. The Rates for Weekdays are different from Weekends. So I'm trying to somehow distinguish the weekdasy from the weekends. How should I approach it?

 

[codeWarrior456]

You can use the Weekday() function to do this. It takes a date and returns a value between 1 and 7 to indicated the day of the week. Sunday = 1 and so forth.

 

[hongbin81]

What's the syntax?

 

[hongbin81]

OH........k

 

[MichaelRed]

Ckeck Weekday or WeekDayName in {F1}. But since you are into Zoned times, you will soon also want and need to do another set of "Hoilday" rates, which are again different.

Otherwise the code presented is questionable. Unless other processes coerce the duratiuon to be the EXACT interval, it will fail to provide any rate. A more robust soloution would use the "To" predicate of the Secect Case block to always check a RANGE of [duration].


Further, hte enyire scheme is a maintenance disaster. It "looks like" the typical rate schedual for telecommunications, which are subject to frequent and unpredictable variance. To reasonably maintain such a system more-or-less "begs" for a 'Database' (i.e. TABLE) soloution, so the code may remain unchanged, and only the "data" need be revisited when there are 'upheavals'.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places