• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

Referencing records in a table

I have a table (tblHoliday) with a list of dates (34 records).

My form code (based on a different table) uses a For...Next Statement to count the number of weekdays between two selected dates.  An embedded If statement counts only the weekdays.  (Brewdog, if you're reading this it may look familiar).

I don't want to add all weekdays, though.  I need to eliminate the dates listed in tblHoliday.

I am pretty sure I want to embed this in the weekday If statement so that each weekday is compared to the holiday list and not counted if it matches, but I have little idea how to reference the dates in the table.

Thanks in advance,
WM
0
wmaster1111
Asked:
wmaster1111
  • 2
  • 2
1 Solution
 
brewdogCommented:
wmaster1111:

I did indeed recognize the question. :o) I just went and played with this a bit, and here's what I came up with:

Private Sub VacationUsed()

    Dim iCheck As Integer
    Dim i As Integer

    txtDaysUsed = 0
    If Not IsNull(txtEnd) And Not IsNull(txtStart) Then
        iCheck = DateDiff("d", txtStart, txtEnd) + 1
        Select Case iCheck
            Case 0
                txtDaysUsed = 1
            Case Is < 0
                MsgBox "You have entered an invalid date range. Remember that the Start of your vacation needs to come before the End of your vacation."
                txtDaysUsed = 0
            Case Else
                For i = 0 To iCheck - 1
                    If (WeekDay(txtStart + i) >= 2 And WeekDay(txtStart + i) <= 6) Then
                         If IsNull(DLookup("HolidayDate", "tblHolidays", "HolidayDate = #" & txtStart + i & "#")) Then
                            txtDaysUsed = txtDaysUsed + 1
                        End If
                    End If
                Next i
        End Select
    End If
   
End Sub

Two things to note here:
1. I changed the calculation of the days between start and end. I hadn't thought about it before, but the calculation as you have it (unless you already caught this) will only take the number of days *between* start and end dates. That means if I sign up for Monday through Friday, it will only count four (6-2). So here, I added a 1 to the DateDiff calculation.

2. I created a table called tblHolidays which had the holidays for our company stored in it. The key field was "HolidayDate" which is a date/time field storing the calendar date on which the holiday will be taken. Unfortunately, this would have to be updated every year, but . . . anyway, what I do is look up the current value of txtStart + i, the number of days we've looped; if it's in the holiday table, I don't increment vacation days used. If it isn't in the table, it's a vacation day.

Hope this works for you . . . I'll have to suggest this for our own HR department to use! :o)

brewdog
0
 
wmaster1111Author Commented:
Thanks!  I tried it out and it seems to work perfectly.  I caught the error in your previous code, but I corrected it in "Case Else" by using "For i=0 to iCheck" (I took out the "-1") and it works fine.  

One thing to note on adding 1 to the inital iCheck definition, as you did in this example, is that you will need to change "Case 0" to "Case 1" and "Case is <0" to "Case is <1".

My VB knowledge is a bit limited, as you can probably tell from my questions.  Do you have any recommendations for any books or websites where I could teach myself more VB at this level (learning the functions like DLookup, DateDiff, and Weekday)?  I already understand the Do loops, Cases, and If statements, so I don't need anything covering that.

Thanks again,
WM
0
 
brewdogCommented:
hmm . . . the only book I've used much is the Access Developer's Handbook (I've looked through the 2 and 97 versions, both of which are excellent) published by Sybex. I don't know of any comprehensive source that explains functions, etc. In fact, because of that, I've created my own cheat sheets that I use when I train others in Access. if you're interested, I could send you a list of handouts I have and you could "order" some if you like. :o) They're all free.

If that sounds appealing, just post your e-mail address and I'll send a list.
0
 
wmaster1111Author Commented:
That would be great.  Please send the list to rparma@edgepet.com .

Thanks again.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now