Solved

Date Diff in Expression builder Access

Posted on 2009-04-15
7
503 Views
Last Modified: 2013-11-29
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
0
Comment
Question by:siva_iaf
  • 4
  • 3
7 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24146976
There is this
http://www.mvps.org/access/datetime/date0006.htm
but do you also want to consider public holidays?
0
 

Author Comment

by:siva_iaf
ID: 24146996
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24147049
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 65

Expert Comment

by:rockiroads
ID: 24147055
Ageing: WorkingDays(BirthdateAs, AmendDate)
0
 

Author Comment

by:siva_iaf
ID: 24147130
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24147255
u can use it in a update statement like thos

update mytable
set myfield = workingdays(somedate, someotherdate)
0
 

Author Closing Comment

by:siva_iaf
ID: 31570369
Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question