Date Diff in Expression builder Access

In my expression builder i am creating a Field called ageing as

Ageing:  [TblAfterCompare]![BIRTHDATEAS] - [TblAfterCompare]![AmendDate]

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
siva_iafAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rockiroadsConnect With a Mentor Commented:
u can use it in a update statement like thos

update mytable
set myfield = workingdays(somedate, someotherdate)
0
 
rockiroadsCommented:
There is this
http://www.mvps.org/access/datetime/date0006.htm
but do you also want to consider public holidays?
0
 
siva_iafAuthor Commented:
I need to exclude only the Saturday and Sundays..
Moreover this is giving a Code which i dont wish as I want to have it in a Expression builder in a Query Design Grid.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
rockiroadsCommented:
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



0
 
rockiroadsCommented:
Ageing: WorkingDays(BirthdateAs, AmendDate)
0
 
siva_iafAuthor Commented:
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.
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

Open in new window

0
 
siva_iafAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.