By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
27 Replies


jaxjagfan
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

jaxjagfan
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

ADezii
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

FishVal
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
  1. Public Function DateIntersect(ByVal dteStart As Variant, _
  2.                               ByVal dteEnd As Variant, _
  3.                               ByVal dteLL As Variant, _
  4.                               ByVal dteUL As Variant) As Variant
  5.  
  6.     'calculates intersect of dteStart/dteEnd with dteLL/dteUL
  7.     Dim lngDays As Long
  8.  
  9.     'if any of arguments is null then exit function returning null
  10.     If IsNull(dteStart) Or IsNull(dteEnd) Or _
  11.         IsNull(dteLL) Or IsNull(dteUL) Then Exit Function
  12.  
  13.     If dteStart < dteLL Then dteStart = dteLL
  14.     If dteEnd > dteUL Then dteEnd = dteUL
  15.     lngDays = DateDiff("d", dteStart, dteEnd)
  16.     If lngDays < 0 Then
  17.         DateIntersect = 0
  18.     Else
  19.         DateIntersect = lngDays + 1
  20.     End If
  21.  
  22. End Function
  23.  
It may be used in query in the following way.

Expand|Select|Wrap|Line Numbers
  1. SELECT DateIntersect(tbl.dteStart,tbl.dteEnd,#1/16/2005#,#2/15/2005#) AS qqq
  2. FROM tbl;
  3.  
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

jaxjagfan
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

ADezii
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

FishVal
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

jaxjagfan
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

FishVal
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

ADezii
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
  1. Private Sub Command76_Click()
  2. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  3. Dim dteRangeStart As Date, dteRangeEnd As Date, dteDateToCompare As Date
  4. Dim MySQL As String, MyRS_2 As DAO.Recordset, intDaysWorked As Integer
  5. Dim strSQLUniqueNames As String, intCounter As Integer, strRowSource As String
  6.  
  7. 'Set up the lstTotalDaysWorked List Box
  8. With Me!lstTotalDaysWorked
  9.   .ColumnCount = 2
  10.   .ColumnWidths = "1.5 in;1 in"
  11.   .Width = 2.5 * 1440
  12.   .RowSourceType = "Value List"
  13. End With
  14.  
  15. 'Define your Start and End Dates for the Range
  16. 'Start Date is on the 16th of the Previous Month
  17. dteRangeStart = DateSerial(Year(Now()), Month(Now()) - 1, 16)
  18.  
  19. 'End Date is on the 15th of the Current Month
  20. dteRangeEnd = DateSerial(Year(Now()), Month(Now()), 15)
  21.  
  22. 'Create a SQL String to define Unique Names in Ascending Order
  23. strSQLUniqueNames = "Select Distinct [Name] From tblDaysWorked Order By [Name];"
  24.  
  25. Set MyDB = CurrentDb
  26. 'Create a Recordset based on Unique Names
  27. Set MyRS = MyDB.OpenRecordset(strSQLUniqueNames, dbOpenSnapshot)
  28.  
  29. Do While Not MyRS.EOF   'Loop through Unique Names
  30.   'Retrieve all Date Ranges worked for each Unique Name (MyRS_2)
  31.   MySQL = "Select * From tblDaysWorked Where [Name] = '" & MyRS![Name] & "'"
  32.   Set MyRS_2 = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)
  33.     Do While Not MyRS_2.EOF
  34.       For intCounter = 0 To DateDiff("d", MyRS_2![StartDate], MyRS_2![EndDate])
  35.         dteDateToCompare = DateAdd("d", intCounter, MyRS_2![StartDate])
  36.           If dteDateToCompare >= dteRangeStart And dteDateToCompare <= dteRangeEnd Then
  37.             'Within specified Range - keep a Running Total
  38.             intDaysWorked = intDaysWorked + 1
  39.           End If
  40.       Next
  41.       MyRS_2.MoveNext
  42.     Loop
  43.     strRowSource = strRowSource & MyRS![Name] & ";" & intDaysWorked & ";"
  44.     intDaysWorked = 0     'Reset for the Next individual
  45.   MyRS.MoveNext
  46. Loop
  47.  
  48. Me!lstTotalDaysWorked.RowSource = Left$(strRowSource, Len(strRowSource) - 1)
  49.  
  50. MyRS_2.Close: MyRS.Close
  51. Set MyRS_2 = Nothing: Set MyRS = Nothing
  52. 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

FishVal
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

FishVal
Expert 2.5K+
P: 2,653
You are welcome.

Best regards,
Fish
Jan 8 '08 #28

Post your reply

Sign in to post your reply or Sign up for a free account.