VB Code Question 1

[foxhound4]

Ok...I work at a Police Department and I was asked to create a databse for their time...However I have an issue where I need to code something and I am at a loss.

The Police officers work four days and have two days off. Those two days off are called RDOs. I need to write some code that checks the current Date to see if it is an RDO.

Any suggestions?

TIA!!!

 

[RickSpr]

Obviously, such a function needs to depend on the particular police officer, since different dates are RDOs for different officers. I'd suggest you'll have to store a reference date for each officer. Let's say you store the date of the first of two RDOs for each officer, and you have that date available when you need to test for an RDO. You can then use the following function to determine whether a given date is an RDO for that officer.

  Public Function IsRDO(TestDate as Date, RefDate As Date) As Boolean
      Dim n As Long

      n = DateDiff("d", TestDate, RefDate) Mod 6
      IsRDO = (n = 0) Or (n = 1)
  End Function

You'd call the function like this:

    If IsRDO(Date(), OfficerRefDate) Then...

Note: This function only gives correct results of the reference date is earlier than the test date. Rick Sprague

 

[oneshadow]

You can check the name of the day by using Weekday function. This function returns variant (integer), so you need put the day's name manually.

Dim iDay As Integer
Dim sDayName As String

iDay = Weekday(Format(Date, "MM/DD/YYYY&quot)
Select Case iDay
Case 1
sDayName = "Sunday"
...
Case 7
sDayName = "Saturday"
End Select


Once you have the day's name, you can cross check with the officer master file. See his RDO, if it is the same then today he is off.

 

[foxhound4]

RICKSPR:

Thanks for the help...works great with one problem. I have officers with their first RDO on 2/5. 2/23 should be an RDO for them but is returning that it is not. The function is saying that someone who started 2/2 has an RDO on 2/23. That is not right...any suggestions? Thanks again for your help?

 

[foxhound4]

Rick:

Scratch that last message that was my fault. However I am still running into a problem. I have an officer whose first RDO is on 2/5. Therefore 2/23 and 2/24 should be RDOs for him. It says that the 23rd is however when I change the date on my machine to the 24th it says that it is not an RDO for him. Any suggestions? Thanks again for all your help, I do appreciate it!!!

George

 

[foxhound4]

Rick,

One last time. In the function I switched the RefDate and the TestDate and now the function works fine so I guess maybe you had swicthed them? Anyway, thanks a lot man you're a life saver!!

George

 

[RickSpr]

You're right - I had them backward. I once wrote a DateDiff function for mainframe applications, and did the calculation by subtracting the second date from the first. VBA does the opposite, and I tend to forget that. Don't know why I didn't catch it in testing last night.

Glad I could help. Rick Sprague

 

[foxhound4]

Rick or anyone:

One final question...I have everything working well with the funtion...one final problem...I have set the form up as continuous and have already defined the value of OfficerRefDate. However, on the continuous form, I would like the field for officers with an RDO to have a red forecolor...the field for officers who do not have an RDO to have a blue forecolor and the field for officers designated as "Flex" to have a green forecolor. The code below os what I have but when I open the form...if I click in an officer with an RDO...all officer names appear red...if I click on an officer without an RDO all officer fields become blue...how do I set this up so it only changes the forecolor for individual officers? TIAA!!!!!

CRecord = Me.CurrentRecord
MyForm = Me.Recordset

For Each CRecord In MyForm
If IsRDO(Date, OfficerRefDate) Then
Officer_Name.ForeColor = 255
ElseIf Officer_Shift.Value = "Flex" Then
Officer_Name.ForeColor = 4259584
Else
Officer_Name.ForeColor = 16711680
End If
Next