•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS Access and FileMaker Pro section within the Web Development category of DaniWeb, a massive community of 361,909 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,572 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS Access and FileMaker Pro advertiser:
Views: 10843 | Replies: 5
 |
•
•
Join Date: Jul 2006
Posts: 3
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Hi, I have an access database that I use for recording the sickness of my employees. One of the tables holds the dates, Start Date, End Date and Count of Days etc. I need to produce a query that will show me a count of all of these dates between two date parameters that I will input when the report is run. However, to complicate matters, I need a definitive count even if the end date runs over the end date of my query.
For example....
I am employee X and I am sick between...
Start_Date = 01/07/2006
End_Date = 03/08/2006
If I wanted to run a report on this employee I would type in a start date and end date of 01/07/2006 to 31/07/2006 (i.e. the calendar month). This would need to take into account that the employee's end date is not in July. Presently, this doesn't happen unless the employee is off sick and returned within my parameter query.
Any idea how I can do this? It is driving me nuts, particularly as I often need to report on absences for a calendar month.
For example....
I am employee X and I am sick between...
Start_Date = 01/07/2006
End_Date = 03/08/2006
If I wanted to run a report on this employee I would type in a start date and end date of 01/07/2006 to 31/07/2006 (i.e. the calendar month). This would need to take into account that the employee's end date is not in July. Presently, this doesn't happen unless the employee is off sick and returned within my parameter query.
Any idea how I can do this? It is driving me nuts, particularly as I often need to report on absences for a calendar month.
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,161
Reputation:
Rep Power: 7
Solved Threads: 58
Search on start date only.
So your criteria for Start_Date would be: Between #01/07/2006 And #31/07/2006
So your criteria for Start_Date would be: Between #01/07/2006 And #31/07/2006
Last edited by hollystyles : Jul 16th, 2006 at 3:26 pm.
•
•
Join Date: Jul 2006
Posts: 3
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
•
•
•
•
Originally Posted by hollystyles
Search on start date only.
So your criteria for Start_Date would be: Between #01/07/2006 And #31/07/2006
Not exactly, I could have somebody off say from 25/06/06 to 10/08/06, but I would like a query / report that shows me how many days were lost between a given date of my choosing, i.e. 01/07/06 to 30/07/06. The calculation would therefore need to count the number of days lost for this person, taking into consideration that the start date and end date are both before and after the date range of my query.
I need this because I often have to report the number of days lost per month, without any consideration for when the person actually went off sick or returned. For example...
Sick from 26/06/06 to 07/07/06, my query asks for a count of days lost between 01/07/06 and 30/07/06 for this person, it would then come back as 5 working days lost. In order words, it would be a definitive count of the number of days lost, regardless of when the person went off sick or returned.
!!! You can see why it is driving me nuts. All I can do at the moment is get it to calculate the number of days between two dates if both the start date and end date fall within my query range, which is unlikely.
Thanks. David.
•
•
Join Date: Dec 2004
Location: Lincoln Park, Michigan
Posts: 1,744
Reputation:
Rep Power: 7
Solved Threads: 106
Hmn.... are you/can you use a macro or the VB Editor? If so, you could probably build a macro for this... So, what I mean is, you could have the Query return the entire time off, and then have a macro get the number of days between your two parameters, right?
Last edited by Comatose : Jul 16th, 2006 at 9:47 pm.
•
•
Join Date: Jul 2006
Posts: 3
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
•
•
•
•
Originally Posted by Comatose
Hmn.... are you/can you use a macro or the VB Editor? If so, you could probably build a macro for this... So, what I mean is, you could have the Query return the entire time off, and then have a macro get the number of days between your two parameters, right?
Yep. I can use VB and Macro's but am not very good with the code. Any idea what type of code I would need for this? If so, I could probably alter the name of the database tables etc to accomodate.
Many thanks.
David.
•
•
Join Date: Dec 2004
Location: Lincoln Park, Michigan
Posts: 1,744
Reputation:
Rep Power: 7
Solved Threads: 106
Here is a Pretty quick piece of code I crufted up....
Naturally, I would imagine that you are going to want to change things so that it's a little more friendly to you, I'm not sure.... but the magic behind how it works is the "DateDiff" function. The DateDiff function calculates the difference between 2 dates, but it has to know what interval it should use..... do you want to know how many months, days, years, etc? So, that's the first piece of info you have to give datediff. In The Example above, we use a variable (RetUnit) that will contain that information based on what you type in the last popup box. So, if you want it in Days, it will automatically use DateDiff with "d" as the first parameter. If you know that you will only ever use this with Days, Then you could simply change the Line
Also, another little Gem that I'd thought I'd throw into the mix here, is that the DateDiff function can be used in a Query.... Uh Oh. I don't do much with Queries, and you may have to modify for a different version of access or whatever (not sure), but I googled around and found the image attached below. I'm not sure if it would be easier for you to use the code in a macro or module, or just modify your Query to look something like the attachment, but either way, let me know how it turns out.
' /* Declare Our Variables */
Dim d1
Dim d2
Dim RetUnit
Dim Diff
' /* Try To Get The Starting Date */
d1 = InputBox("Please Enter The Starting Date")
If d1 = "" Then Exit Sub
' /* Try To Get The Ending Date */
d2 = InputBox("Please Enter The Ending Date")
If d2 = "" Then Exit Sub
' /* Try To Get The Interval Unit To Return (Days Off, Weeks Off, etc) */
RetUnit = InputBox("Day, Week, Month, or Year?")
If RetUnit = "" Then
Exit Sub
Else
' /* Set The Interval Code Based On The Users Selection */
If lcase(RetUnit) = "day" Then
RetUnit = "d"
ElseIf lcase(RetUnit) = "week" Then
RetUnit = "ww"
ElseIf lcase(RetUnit) = "month" Then
RetUnit = "m"
Else
RetUnit = "yyyy"
End If
End If
' /* Calculate The Difference Between Date1, and Date2, With The Chosen Interval */
Diff = DateDiff(RetUnit, d1, d2)
' /* Show The User */
MsgBox "The Difference Is " & DiffDiff = DateDiff(RetUnit, d1, d2) to read Diff = DateDiff("d", d1, d2) and it will return the value in Days Only (Always). Here Is A Complete List Of Those Interval Codes:yyyy Year q Quarter m Month y Day of year d Day w Weekday ww Week h Hour n Minute s Second
Last edited by Comatose : Jul 17th, 2006 at 5:41 am.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MS Access and FileMaker Pro Marketplace
Linear Mode
- DATETIME data type arithmenic in SQL Server 2005 (MS SQL)
- Calculating Time Difference (C++)
- Having a little trouble with my Code. [please Help me out] (C++)
Other Threads in the MS Access and FileMaker Pro Forum
- Previous Thread: shortcut on client to connect to remote database filemaker
- Next Thread: foxpro help
