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
wmaster1111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.