Days of week to Code

[tdmondoman]

I have a database that stores days of the week similar to that of VB's
message box button values. It is used to store access days of staff key
rings. Each day has a value, and the total for which days staff have access
is stored within the table. For example:

S = 64
M = 32
T = 16
W = 8
T = 4
F = 2
S = 1

So if staff had access on all days, (SMTWTFS) the total 127 would be stored.
If staff had access on only monday thru friday (-MTWTF-) then the total
stored would be 62. If staff only had access on monday, 32 would be stored.
Etc...

Now, what I want to do is take the number (which is put in place by another
application) and display it in a report as SMTWTFS format. But I dont know
how to take the sum and break in down back into its individual numbers.

Any help on this would be much appreciated

 

[ghetto_banjo]

I think you need if statements with logic like

dim result as string

value = total number

if value > 64 then
result = result & "Su"
value = value - 64
end if

if value > 32 then
result = result & "M"
value = value - 32
end if

etc

That might help you get started.

 

[ghetto_banjo]

Make those >= in the if statements actually

 

[tedmi]

That will not work. Suppose the value is 64, meaning that access was on
Sunday only. All of the IF's will test TRUE! The procedure for power-of-2
testing is as follows:
If (Value AND 64) > 0 Then Result = "Su"
If (Value AND 32) > 0 Then Result = Result & "M"
If (Value AND 16) > 0 Then Result = Result & "T"
etc.

It might look better on a report if days with no access were represented by
blanks or *'s in the string, instead of running access days together. In
that case, do this:
Iif ((Value AND 64) > 0, "Su", "*")
Iif ((Value AND 32) > 0, Result & "M", Result & "*")
etc.

 

[Klatuu]

Actually, it does work. The And is a bitwise comparision operator. Notice
the parenthises around the operation.

Here is a function I wrote that demonstates how it does work. You pass it
the number and it will return a string with all the days included separated
with spaces based on the number passed.

Public Function WeekDayList(lngDays) As String
Dim lngNum As Long
Dim strDays As String

lngNum = 64
Do Until lngNum = 0
If (lngNum And lngDays) Then
Select Case lngNum
Case 64
strDays = strDays & " Sunday "
Case 32
strDays = strDays & " Monday "
Case 16
strDays = strDays & " Tuesday "
Case 8
strDays = strDays & " Wednesday "
Case 4
strDays = strDays & " Thursday "
Case 2
strDays = strDays & " Friday "
Case 1
strDays = strDays & " Saturday "
End Select
End If
lngNum = lngNum / 2
Loop
WeekDayList = Replace(strDays, Space(2), Space(1))

End Function

Maybe you should test a suggestion before you say it doesn't work.

 

[Ken Sheridan]

What you have here is a set of flags stored as a 7-bit integer variable.
Each bit represents one day of the week. In binary notation 127 is 1111111,
so this represents all days of the week as every bit is turned on. 62 in
binary notation is 0111110, i.e. only the bits for Monday to Friday are
turned on.

To determine whether a bit is turned on a Boolean AND operation is used.
This will return the value where both bits are turned on so:

0111110
AND
0000100

will return 4 (in decimal notation), because only the bit representing 4 is
turned on in both.

So you can wrap the logic up in a function by inserting the following module
into your database. Just paste the code into a new standard module and save
it as something like mdlDayStuff:

''''module begins''''
Option Compare Database
Option Explicit

Public Const conSAT As Integer = &H1
Public Const conFRI As Integer = &H2
Public Const conTHU As Integer = &H4
Public Const conWED As Integer = &H8
Public Const ConTUE As Integer = &H10
Public Const ConMON As Integer = &H20
Public Const ConSUN As Integer = &H40


Public Function GetDays(intEmpDayFlags As Integer) As String

Dim i As Integer
Dim intDay As Integer
Dim strDays As String

For i = 7 To 1 Step -1
intDay = (intEmpDayFlags And (2 ^ i) / 2)
Select Case intDay
Case ConSUN
strDays = strDays & "S"
Case ConMON
strDays = strDays & "M"
Case ConTUE
strDays = strDays & "T"
Case conWED
strDays = strDays & "W"
Case conTHU
strDays = strDays & "T"
Case conFRI
strDays = strDays & "F"
Case conSAT
strDays = strDays & "S"
Case Else
strDays = strDays & "-"
End Select
Next i

GetDays = strDays

End Function


Public Function HasAccess(intEmpDayFlags As Integer, _
intAccessDayFlags As Integer)
As Boolean

HasAccess = ((intEmpDayFlags And intAccessDayFlags) = intAccessDayFlags)

End Function
'''module ends''''

Note how its common to use hexadecimal notation to declare the constants in
cases like this.

To return a string showing which days the value stored in the column in the
table represents call the GetDays function, passing the value into it as its
argument, so in your report add an unbound text box with a ControlSource
property of:

=GetDays([YourFieldNameGoesHere])

If the value is 62 this will return -MTWTF-, if its 64 it will return
S-----S for instance.

You'll see that I've also included a HasAccess function in the module, to
demonstrate how you are not restricted to determining whether one particular
bit is turned on in both values; you can also determine if a set of bits are
turned on in both. So to see if an employee has access on Saturdays and
Sundays for example you can compare their value with 65 (64+1). If an
employee has 7-day access for instance:

=HasAccess([YourFieldNameGoesHere], 65) returns True because (127 AND 65) =
65, the first and seventh bits being turned on in both cases, but if they
have access from Sunday to Friday only for instance then it returns False
because (126 AND 65) returns 64, not 65, the Saturday bit (1) being turned on
only in 65 (64+1), not in 64.

Finally you'll notice that the constants are declared as Public, making then
available throughout the database (but not in queries) so you can use these
rather than their numeric values, e.g. to see if an employee has weekend
access:

=HasAccess([YourFieldNameGoesHere], conSAT + conSUN)

Ken Sheridan
Stafford, England

 

[tedmi]

Yes, the AND operartion in parens work - that's the one I wrote. What doesn't
work is ghetto_banjo's successive <= compares.

 

[Ken Sheridan]

I think you've failed to notice that he's decrementing the value after each
comparison, so it will work. I don't find it a very elegant method, however.

Ken Sheridan
Stafford, England

 

[BruceM]

It was successive >= comparisons, not <=.

It seems to me the value variable is recalculated after each If. If the
starting value is 98 (Sunday, Monday, Friday) the tests are:

If value (i.e.98) >= 64 Then
result = result & "Su"
value = value - 64 (i.e. 34)
End If

If value (i.e. 34) >= 32 Then
result = result & "M"
value = value - 32 (i.e. 2)
End If

The rest of the If statements will fail (and result will be unchanged)
until:

If value (i.e.2) >= 2 then
result = result & "F"
value = value - 2 (i.e.0)
End If

I'm not saying it is the best way (nor am I saying it is not a good way to
do it). Rather, I am saying I think it will work. If I have missed
something I would be glad to learn what it is.

 

[Klatuu]

Correct. Sorry about that. I thought about it on the way home and realized
I was looking at your version, not his.

 

[Ken Sheridan]

Nevertheless, Dave, you were right the first time; the (amended) solution
posted by ghetto_banjo will work, because the value variable is being
decremented (as Bruce also spotted) whenever an If statement evaluates to
True, so any of the following If statements will only evaluate to True if the
remaining value is >= the bit being examined. It’s an approach I've seen
used before, but as I said, its not an elegant one.

Ken Sheridan
Stafford, England