425,829 Members | 673 Online
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

# Still This Payroll Problem Really Requires a Genius Solution

 P: 16 Hello Experts, I have a Table that assigns periods to employees. The data are like the following Name StartDate EndDate X 14/11/07 17/12/07 Y 18/11/07 10/12/07 My problem is that I want to calculate the days that each employee worked but I ONLY want to calculate the days that are intersecting with this period : (16/11//07) - ( 15/12/07) or generally between the 15th of the current month and the and the 16th of the previous month. In other words, I need to calculate only the days that are related to this month Ignoring the days that are out of the range. I do need your HELP THANK YOU Dec 31 '07 #1
27 Replies

 Expert 100+ P: 254 Hello Experts, I have a Table that assigns periods to employees. The data are like the following Name StartDate EndDate X 14/11/07 17/12/07 Y 18/11/07 10/12/07 My problem is that I want to calculate the days that each employee worked but I ONLY want to calculate the days that are intersecting with this period : (16/11//07) - ( 15/12/07) or generally between the 15th of the current month and the and the 16th of the previous month. In other words, I need to calculate only the days that are related to this month Ignoring the days that are out of the range. I do need your HELP THANK YOU Select Name, DateDiff("d", BeginDate, EndDate) as DaysWorked from YourTableName. DateDiff will give you the difference between 2 dates in days, months, years, etc - d = days. Dec 31 '07 #2

 P: 16 Thanks Jax but the problem is much more complicated than that. I need to calculae the difference in days but only that are in the range of a month as I said in first post. Dec 31 '07 #3

 Expert 100+ P: 254 Thanks Jax but the problem is much more complicated than that. I need to calculae the difference in days but only that are in the range of a month as I said in first post. Need 3 queries. The 1st for StateDate (qry1). 2nd for EndDate (qry2). 3rd to Union together. Select Name, Month(StartDate) as Month,iif Month(StartDate) <> Month(EndDate), DateDiff("d",StartDate,DateAdd("d",-1,CDate("1/" & Month(StartDate) +1 & "/" & Year(StartDate))), DateDiff("d", StartDate, EndDate) as DaysWorked from YourTableName Select Name, Month(StartDate) as Month,iif Month(StartDate) <> Month(EndDate), DateDiff("d",DateAdd("d",-1,CDate("1/" & Month(EndDate) & "/" & Year(EndDate)),EndDate), 0) as DaysWorked from YourTableName Select qry1.* from qry1; UNION Select qry2.* from qry2; The 1st query will handle the time frame from the StartDate to Start Month End or StartDate to EndDate if in same month. The 2nd query will handle the time from End Month Begin to EndDate or 0 if StartDate and EndDate are in the same month as this is calc'd in the 1st query. Dec 31 '07 #4

 P: 16 Thank You Jax, and I am sure this will solve the problem I just will be able to test this and reply back tomorrow morning Even I can't wait to test it So Thank You again for Help Dec 31 '07 #5

 P: 16 Hello JAX, Sorry For being Late,, Infact I'm facing some problems in applying the sql statement,, I did try to do it alot but I am not that experienced,, so please give me some details , how should I construct the query, or what do I have to paste in sql. consider that the table is called (schedule). I don't know what is wrong exactly but it gives me syntax error.. Thank you Jax Jan 1 '08 #6

 Expert 5K+ P: 8,623 Hello JAX, Sorry For being Late,, Infact I'm facing some problems in applying the sql statement,, I did try to do it alot but I am not that experienced,, so please give me some details , how should I construct the query, or what do I have to paste in sql. consider that the table is called (schedule). I don't know what is wrong exactly but it gives me syntax error.. Thank you Jax I'm pretty sure that I can provide a code based solution to your dilemma, but it may be a little involved. I will only attempt to write the code if you are truly interested in this alternative, should this be the case, just let me know. Jan 1 '08 #7

 Expert 2.5K+ P: 2,653 Hi, Jax. Hello Experts, I have a Table that assigns periods to employees. The data are like the following Name StartDate EndDate X 14/11/07 17/12/07 Y 18/11/07 10/12/07 My problem is that I want to calculate the days that each employee worked but I ONLY want to calculate the days that are intersecting with this period : (16/11//07) - ( 15/12/07) That is quite simple. The function below will return date ranges intersection in days. Expand|Select|Wrap|Line Numbers Public Function DateIntersect(ByVal dteStart As Variant, _                               ByVal dteEnd As Variant, _                               ByVal dteLL As Variant, _                               ByVal dteUL As Variant) As Variant       'calculates intersect of dteStart/dteEnd with dteLL/dteUL     Dim lngDays As Long       'if any of arguments is null then exit function returning null     If IsNull(dteStart) Or IsNull(dteEnd) Or _         IsNull(dteLL) Or IsNull(dteUL) Then Exit Function       If dteStart < dteLL Then dteStart = dteLL     If dteEnd > dteUL Then dteEnd = dteUL     lngDays = DateDiff("d", dteStart, dteEnd)     If lngDays < 0 Then         DateIntersect = 0     Else         DateIntersect = lngDays + 1     End If   End Function   It may be used in query in the following way. Expand|Select|Wrap|Line Numbers SELECT DateIntersect(tbl.dteStart,tbl.dteEnd,#1/16/2005#,#2/15/2005#) AS qqq FROM tbl;   or generally between the 15th of the current month and the and the 16th of the previous month. Hmm. That is more complicated. I will look for a solution. It may require additional table with list of months. Regards, Fish Jan 2 '08 #8

 Expert 100+ P: 254 Hello JAX, Sorry For being Late,, Infact I'm facing some problems in applying the sql statement,, I did try to do it alot but I am not that experienced,, so please give me some details , how should I construct the query, or what do I have to paste in sql. consider that the table is called (schedule). I don't know what is wrong exactly but it gives me syntax error.. Thank you Jax Here we go - step by step. No SQL till we get to the UNION query. Go to queries tab. Create a new query in design view. Select the table that holds the data in question (I called it "YourTableName" in this example). 1. Select the "Name" field as the first column in your query. 2. The 2nd column should look like this: Month: Month(StartDate) 3. The Third will look like this: DaysWorked: iif Month(StartDate) <> Month(EndDate), DateDiff("d",StartDate,DateAdd("d",-1,CDate("1/" & Month(StartDate) +1 & "/" & Year(StartDate))), DateDiff("d", StartDate, EndDate) Run query and test results. This one covers the 1st half of equation. Save query as "qryDaysWorked1" Now do a "SaveAs" and save it as "qryDaysWorked2" Modify the third column as follows: DaysWorked: iif Month(StartDate) <> Month(EndDate), DateDiff("d",DateAdd("d",-1,CDate("1/" & Month(EndDate) & "/" & Year(EndDate)),EndDate), 0) Run query and test results. This one covers the 2nd half of equation. Save the query. Create a 3rd query. Create a query in design view but do not select a table and close the dialog. Click on the button on toolbar that displays "SQL". Paste the following: Select qryDaysWorked1.* from qryDaysWorked1; UNION Select qryDaysWorked2.* from qryDaysWorked2; Run query and test results. This one pulls the results of the other 2 together. Save this query with any name you want. FYI - You can do this whole process with one query but I tried to simplify it as best as I could. The "iif" works just like an "if" in Excel with the same layers and limitations. You can build custom functions as the others have suggested if you are ready for that. Jan 2 '08 #9

 Expert 5K+ P: 8,623 Hello Experts, I have a Table that assigns periods to employees. The data are like the following Name StartDate EndDate X 14/11/07 17/12/07 Y 18/11/07 10/12/07 My problem is that I want to calculate the days that each employee worked but I ONLY want to calculate the days that are intersecting with this period : (16/11//07) - ( 15/12/07) or generally between the 15th of the current month and the and the 16th of the previous month. In other words, I need to calculate only the days that are related to this month Ignoring the days that are out of the range. I do need your HELP THANK YOU I have a code-based solution that factors in worked days only within a specified Range, namely, 16th day of the Previous Month ==> 15th of the Current Month. Results are also conveniently written to a List Box on a Form activated by the Click() Event of a Command Button. I won't Post it unless you specifically request it. Jan 2 '08 #10

 P: 16 Hello Every Body and My deep thanks for Who is Helping Me, InFact JAX the problem with your solution is not something in the function, it is working but, but the function calculates All the days worked in the month without checking if it is in the specified period or not. I beleive that I couldn't write my problem in a clear way. So Thank You Jax and sorry of letting you misunderstand my problem and waste your time Jan 2 '08 #11

 P: 16 Thank You ADezii for offering your help,, But I don't actually understand what you mean by solution that will be "Little involved". Anyway Thank you and I need all possible help Jan 2 '08 #12

 P: 16 Hello FishVal, Actually I tried the sql code, and seemed that it will directly work with no problems, but it gave this error: Unidentified function Dateintersect.. I think it must be through a query and not VBA,,, So did I do something wrong,... Please Help Jan 2 '08 #13

 Expert 2.5K+ P: 2,653 Hello FishVal, Actually I tried the sql code, and seemed that it will directly work with no problems, but it gave this error: Unidentified function Dateintersect.. I think it must be through a query and not VBA,,, So did I do something wrong,... Please Help Hello AccProg, The code of DateIntersect() function I've posted has to be put to a public code module. Then it will be invoked in the query. Jan 2 '08 #14

 P: 16 Hello, ADezii,,, I do need your help,,,, and PLEASE post me your solution,, But I have one question will I be able to retreive the results in a table for keeping History. or Will it be momentary calculation,,, Sorry For Asking these questions but you are the expert not me. Or can I specify in the form the month that I want to generate payroll IN ALL CASES Thank You for YOUR HELP Jan 2 '08 #15

 P: 16 Sorry FishVal, You have posted your reply While I am posting mine,, I will try yours right away Jan 2 '08 #16

 P: 16 YEAH,, FISHVAL ,, IT WORKED,,,,,,,,,,,,,,,,,IT WORKED IT WORKED Can I Make the fields in the dateintersect in the query dependent on a form so to enter the month or the date that calculate the days upon? This Is the Genius Solution that I am talking About Thank You Thank You THANK YOU Jan 2 '08 #17

 P: 16 Hello FISHVAL,, I think I can manage the dependent query I think,, Don't Bother yourself with these trivial stuff I don't like to ask a question that I can answer myself, or may someone find it so easy I will try to do it,,, And Thank You Jan 2 '08 #18

 Expert 100+ P: 254 I think I totally hosed this one up. Let me see if I grab your scenerio correctly. We can all get lost in the semantics around here. You have employees who worked the entire year or portions of the year. You want to be able to enter a date range and calculate the number of days worked for each month within the selected range. I.E. - For accounting purposes you made need to post portions of the salary in the month it occurred. Emp Month Days Bill 6 15 Bill 7 14 Jon 6 15 Jon 7 2 Jan 7 15 Selected range = 6/16/2007 to 7/14/2007 Bill worked or was employed for entire period Jon quit after 2 days in July Jan just started on July 1 I misunderstood the desire to enter a range. Jan 2 '08 #19

 Expert 2.5K+ P: 2,653 Glad you've got it working. Good luck and happy coding. Best regards, Fish Jan 2 '08 #20

 P: 16 Dear JAX, I am Sorry again If I confused you by my unclear way of explaining my problem,, Your solution did work great,,but my additional conditions that I needed, I couldn't conduct it to you in a right way,, Also I think that when you Found me a NEWBIE you didn't think that I need something that is so specific or a bit more complicated. THANK YOU JAX FOR BEING KEEN TO SOLVE MY PROBLEM SINCE THE FIRST POST. I AM SURE THE PROBLEM WAS A MISCONDUCT OF INFORMATION SO SORRY AGAIN Jan 2 '08 #21

 P: 16 In order Not to Confuse any visitor for this thread. THE PROBLEM HAS BEEN SOLVED THANKS to FISHVAL and it is not STILL any more. CASE CLOSED :-) Jan 2 '08 #22

 Expert 5K+ P: 8,623 Hello Experts, I have a Table that assigns periods to employees. The data are like the following Name StartDate EndDate X 14/11/07 17/12/07 Y 18/11/07 10/12/07 My problem is that I want to calculate the days that each employee worked but I ONLY want to calculate the days that are intersecting with this period : (16/11//07) - ( 15/12/07) or generally between the 15th of the current month and the and the 16th of the previous month. In other words, I need to calculate only the days that are related to this month Ignoring the days that are out of the range. I do need your HELP THANK YOU I may have taken too technical an approach to this problem, or even misread it entirely, but in any event I'll post my code and if anyone has any questions, please feel free to ask. BTW, for simplicity, yea right, all the code is contained within the Click() Event of a Command Button. Must be getting too old for this stuff! (LOL)! P.S. - Way to come up with the solution, FishVal, congratulations! Expand|Select|Wrap|Line Numbers Private Sub Command76_Click() Dim MyDB As DAO.Database, MyRS As DAO.Recordset Dim dteRangeStart As Date, dteRangeEnd As Date, dteDateToCompare As Date Dim MySQL As String, MyRS_2 As DAO.Recordset, intDaysWorked As Integer Dim strSQLUniqueNames As String, intCounter As Integer, strRowSource As String   'Set up the lstTotalDaysWorked List Box With Me!lstTotalDaysWorked   .ColumnCount = 2   .ColumnWidths = "1.5 in;1 in"   .Width = 2.5 * 1440   .RowSourceType = "Value List" End With   'Define your Start and End Dates for the Range 'Start Date is on the 16th of the Previous Month dteRangeStart = DateSerial(Year(Now()), Month(Now()) - 1, 16)   'End Date is on the 15th of the Current Month dteRangeEnd = DateSerial(Year(Now()), Month(Now()), 15)   'Create a SQL String to define Unique Names in Ascending Order strSQLUniqueNames = "Select Distinct [Name] From tblDaysWorked Order By [Name];"   Set MyDB = CurrentDb 'Create a Recordset based on Unique Names Set MyRS = MyDB.OpenRecordset(strSQLUniqueNames, dbOpenSnapshot)   Do While Not MyRS.EOF   'Loop through Unique Names   'Retrieve all Date Ranges worked for each Unique Name (MyRS_2)   MySQL = "Select * From tblDaysWorked Where [Name] = '" & MyRS![Name] & "'"   Set MyRS_2 = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)     Do While Not MyRS_2.EOF       For intCounter = 0 To DateDiff("d", MyRS_2![StartDate], MyRS_2![EndDate])         dteDateToCompare = DateAdd("d", intCounter, MyRS_2![StartDate])           If dteDateToCompare >= dteRangeStart And dteDateToCompare <= dteRangeEnd Then             'Within specified Range - keep a Running Total             intDaysWorked = intDaysWorked + 1           End If       Next       MyRS_2.MoveNext     Loop     strRowSource = strRowSource & MyRS![Name] & ";" & intDaysWorked & ";"     intDaysWorked = 0     'Reset for the Next individual   MyRS.MoveNext Loop   Me!lstTotalDaysWorked.RowSource = Left\$(strRowSource, Len(strRowSource) - 1)   MyRS_2.Close: MyRS.Close Set MyRS_2 = Nothing: Set MyRS = Nothing End Sub Jan 3 '08 #23

 P: 16 Thank You ADezii for your solution,,,I will just need some time to try it,,, Thank you Jan 7 '08 #24

 P: 16 Hello FishVal,, I need some help with your solution :) When I tried to subsititute the dates in the query that will determine the range of calculating the working by fields from a form so that to change it easily, it began to miscalculate the numbers and give wrong numbers.. I just need a hint of what I did wrong.. Thank you FishVal for your great HelpS Jan 7 '08 #25

 Expert 2.5K+ P: 2,653 Hello, AccProg. Could you post more details? How do you get form's data in the query? Do you reference it via Forms collection or do you generate SQL string and execute it then? Are the table fields bound to the controls of the form Date/Time type? What date format is applied to the form's controls? Jan 7 '08 #26

 P: 16 Yes FishVal,, I checked my problem through your questions.. It was about this question,, "What date format is applied to the form's controls?" When I checked the controls in the form that I enter the dates through I found it with no specified format,, When I adjusted it to the proper format it gone perferct. It is all ok Now,, Thank You for the Second Time,,,,,,,,, Jan 8 '08 #27

 Expert 2.5K+ P: 2,653 You are welcome. Best regards, Fish Jan 8 '08 #28