siva_iaf
asked on
Date Diff in Expression builder Access
In my expression builder i am creating a Field called ageing as
Ageing: [TblAfterCompare]![BIRTHDA
Birthdate is always earlier or = to Amend Date
But i want the result to give only the count of Business Days (excluding saturdays and sundays), how do i modify it ?
Regards
Ageing: [TblAfterCompare]
Problem with msaccess sql, unlike other sql like t-sql (sql server), you cant do more programmatic stuff. Using the first function in that link, you save it to a module (ensure module name not the same as function name) then can use it in a query
select WorkingDays(BirthdateAs, AmendDate) AS MyDiffDate
select WorkingDays(BirthdateAs, AmendDate) AS MyDiffDate
Ageing: WorkingDays(BirthdateAs, AmendDate)
ASKER
In the code below..I want it to update in TableA Field Name Ageing...on running this query..
I have saved the below mentioend query as Module1.
I have saved the below mentioend query as Module1.
On Error GoTo Err_WorkingDays
Dim intCount As Integer
'StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above
intCount = 0
Do While BirthDate <= AmendDate
'Make the above < and not <= to not count the EndDate
Select Case Weekday(BirthDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
BirthDate = BirthDate + 1
Loop
Ageing = intCount
Exit_WorkingDays:
Exit Function
Err_WorkingDays:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
http://www.mvps.org/access/datetime/date0006.htm
but do you also want to consider public holidays?