MS Access VBA Dateddiff Function

November 17th, 2008, 02:51 AM

Hi,

Please see below Datediff Function It Accepts two dates and returns the number of weekdays between them and also annualleave type and it will check holidays table and it is working....see the code below

************************************************** ***************
Public Function DateDiff(DateFrom As Date, DateTo As Date, Optional AnnualLeaveTypeID As Integer) As Integer

On Error GoTo Err_DateDiff

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Dim Str As String
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

intCount = 0

If AnnualLeaveTypeID = DLookup("[EligibleHolidays]", "tblEligible", "[EligibleHolidays] = " & AnnualLeaveTypeID) = True Then
DateDiff = 0

Else

Do While DateFrom <= DateTo
        rst.FindFirst "[HolidayDate] = #" & DateFrom & "#"
        If Weekday(DateFrom) <> vbThursday And Weekday(DateFrom) <> vbFriday Then
        If rst.NoMatch Then intCount = intCount + 1
    End If

    DateFrom = DateFrom + 1
Loop
    DateDiff = intCount

End If
Exit_DateDiff:
Exit Function

Err_DateDiff:
Select Case Err

Case Else
MsgBox Err.Description

Resume Exit_DateDiff
End Select
End Function

See the Query Below******************

AnnualLeaveType EmpNO DateFrom DateTo NumOfDays
AnnualLeave 1 31-Dec-2007 01-Jan-2008 2
AnnualLeave 2 25-Oct-2008 26-Oct-2008 2
Emergency 1 17-Nov-2008 17-Nov-2008 0

*********************************

My Question is ... how to calculate total for each empNO for specific year(i want to assign combobox on Form 2007/2008/)from the above query, is there any way to modify the VBA Code...

thanks...

mmcdonal · November 17th, 2008, 09:23 AM

Yes. The issue is, what constitutes a year? In your example, Employee 1 takes Annual Leave that spans Calendar years. You may use a Fiscal Year, so you will have spanning issues you need to resolve. Is part of the leave attributed to each year, or to the start year, or the end year?

Also, I thought DateDiff() was a reserved word. How is it that you are getting this public function to work when it already exists?

Where are these results sent to? For example, where are you getting the EmployeeID in this code? Assuming that you are storing these results in a query with the fields you indicated, create a second query that looks like this:

SELECT EmpNo, DatePart("yyyy", DateFrom) As DateYear, Sum(NumOfDays) As SumDays
FROM qryMyQuery
WHERE DateYear = [Forms]![MyForm].[MyYearContol]

This will allow a user to select a year, for example 2007, and show the following results if 2007 is selected:

EmpNo DateYear SumDays
1 2007 2

This assumes the start date is the leave year.

Did any of that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Old Pedant · November 17th, 2008, 04:32 PM

Just as a minor comment: Yes, you can override default functions. But as MMcDonal suggests, the choice of the name DATEDIFF is unfortunate, as it hides access to the builtin function of that same name. It would be much better to choose a different name, in case you decide you need the original DATEDIFF for other purposes at some time.

November 18th, 2008, 01:19 AM

thanks for your reply mmcdonal...
I tried to us this query*****
SELECT EmployeeID, DatePart("yyyy",DateFrom) AS DateYear, Sum(NumOfDays) AS SumDays
FROM qryAnnualLeave
WHERE DateYear=Forms!EmployeeMain.Year;
but there is an Error Message...(You tried to execute a query that does not include the specified expression 'EmployeeID' as part of an aggregate function.)...
Any suggestion please..

SKZ

Old Pedant · November 18th, 2008, 01:54 AM

Yeah, the query is wrong in a couple of ways.

Code:

SELECT EmployeeID, Year(DateFrom) AS DateYear, Sum(NumOfDays) AS SumDays
FROM qryAnnualLeave
WHERE DateYear=Forms!EmployeeMain.Year
GROUP BY EmployeeID, Year(DateFrom)

And presumably you would also want an ORDER BY, but I'll leave that to you.

November 18th, 2008, 08:38 AM

Thanks for your Reply..
this will work for only one field DateFrom....
My question is I am using Datediff Function (Mention Above) in the below query (qryAnnualLeave)
AnnualLeaveID EmployeeID DateFrom DateTo NumOfDays
__________________________________________________ _____________
    1 1 31-Dec-2007 01-Jan-2008 2
    1 1 25-Oct-2008 26-Oct-2008 2
    2 1 17-Nov-2008 17-Nov-2008 0
    1 2 17-Nov-2008 17-Nov-2008 1

Note: NumOfDay(=DateDiff([DateFrom],[DateTo],[AnnualLeaveTypeID])
If AnnualLeaveType = 1 It will do calculation else NumOfDays =0

************************************************** *************
Now I have another Table (tblLeaveAllocation)
EmployeeID AllocationForYear Year DayTaken
      1 30 2008
      1 25 2007
      2 30 2008

Option 1: How to get [DayTaken] total from above query
Option 2 Else I want to calculate on Form:
EmployeeID is on Form,
AllocationForYear:(From tblLeaveAllocation)
Field:Daytaken: (From qryAnnualLeave)
Year(Combobox(2007/2008)

On forms i try to use Dlookup formula from tblLeaveAllocation but is cannot put Criteria for(From EmployeeID & Year)
************************************************** **********

Hope i can get solution ....

thanks

SKZ