Brilliant Minds Desperately Needed for Date Calculations

[lespaul]

I am having some real problems trying to figure out the logic to accomplish my programming task and hopefully one of you brilliant people out there can help me out. Here's what I have:

I am building a program that will produce completed time sheets from entered days of work. The time entry form allows the user to select a day from a DateTimePicker and enter in the start and end time. A query then updates an Access 2000 database with the WorkDate (YYYYMMDD), StartTime, EndTime (HHMM), and TimeWorked (#.##). This is all working beautifully! To Process the time sheets I have the user enter the Month to process from a drop down list (index 0 to 11 - add one to get the month to process). But here's the tricky part. The funds to make payments for the first 22 hours of each week (Sun - Sat) worked come from one source and any hours over 22 get paid from a different source. So if the 1st of the month is on a Wednesday, I need the hours worked in the previous month from Sunday - Tuesday to see if the 22 hours have been fully used. I have most of the process that needs to occur after the separation of payers, but I'm stuck right in the middle.

Do any of you brilliant persons have any ideas to help me over this? Thanks for any direction and guidance!

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[mikeEd]

Is this not just a case of getting the last <dayofweek - 1> days' hours from the previous month? So if the 1st is wednesday - day 4 in your logic - then you need the last 3 days of the previous month?

 

[lespaul]

Exactly! I need the hours worked from the last 3 days of previous month to see if they add up to 22.
For example, October 1 falls on Tuesday. Assume the person worked the following hours:
Sept 29 - 0
Sept 30 - 6
Oct 1 - 5.50
Oct 2 - 6.75
Oct 3 - 6
Oct 4 - 4.50
Oct 5 - 0

The total hours worked is 28.75. The first 22 hours get paid by DVR. Payments are made monthly so Sept 30 was paid with the last payment. But the first 22 hours of the week are:
6 (Sept 30)
+ 5.5 (Oct 1)
+ 6.75 (Oct 2)
[underline]+ 3.75[/underline] (First 3.75 hours of Oct 3)
22.00

So the remaining hours of Oct 3 (2.25) plus any hours on the 4th and 5th are paid from the 2nd source at a different rate.

If the 1st were on a Thurs, then I would need the hours from Sunday to Wednesday.

I have all the data I need to perform the calculation, I just can't get the logic to figure it out!

thanks for your response! I appreciate your taking the time to look it over.



Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[mikeEd]

I'm still not really sure what it is you can't do. Is it an sql query you need?

 

[lespaul]

I can't figure out how to find out how many days back to go to get to the Sunday of the last month if I need to. I get the month from the user (October), the itemindex of October is 9 so I add 1 to get the 10th month, then add to the string so that I get processmonth := 10/1/2002. Then I figure out what day of the week this is (DayOf := DayOfWeek(processmonth) = 3). Then I increment the date by IncDay(processmonth, (1 - DayOf)) which in this case would be '9/29/2002'. So I run my query that brings me all the data from 9/29/2002 through 10/31/2002. So if a boolean value (PrevMonth) is true, I need to add all the hours worked in September, how do I figure out which results in my query are from the previous month..now I feel like an idiot! In trying to explain it to you again, the lightbulb went off! I'll get back to you!!

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned