Windows XP 
Access 2000
Alright, so I've got an Access 2000 database linking to an Excel 2000 sheet with date, name, hours worked, overtime hours, and penalties (in dollars). So far I've made a nice form where you select a date range and a name and you can generate a report that shows the employee's total hours, OT hours, OT%, etc. I linked this to a table in Access that has hourly wages, and included total pay in the report. Everything's good here.
What I need now is a report that shows all of this data per pay cycle. This means choosing a payday and showing regular hours from the previous two weeks and OT hours from the two weeks prior to that. For example, if you entered 2/22/13 as the pay date, the report would show the total hours and costs for regular hours worked from 2/1/13 to 2/15/13 and OT hours worked from 1/18/13 to 2/1/13. I made text boxes in the form that automatically fill in the dates of each range, so I can reference them later.
So far I've tried two things. First, I used an IIF sum statement in a text box in the report itself: =Sum(IIf([Date]=Between [Forms]![FRM_PayDate]![Reg_Start] and [Forms]![FRM_PayDate]![Reg_End],[Reg_Hours],0)). I tried this with and without quotes around the Between statement, to no avail. Not sure if it's possible to use a Between statement like that, or at all inside an IIF.
Next, I tried making a few queries. One contains only the regular hour date range and daily hour counts, another contains only the OT hour date range and daily hour counts, and a third joins the two of those to the original data via joining [Name] and [Date] on all three, but this confusingly only showed me the data for the date 2/1/13. I'm thinking it's because it's the only common date in all three...
I should clarify, I realize I can sum things up in separate queries and just output the totals, but the good thing about the reports I have now is I can go in and change the "Detail" section to Visible, and get a detailed rundown of the numbers instead of just a sum. I'd like this to be the case with the new report as well.
I'm relatively new to Access and totally lost. I don't know much VB coding, but I'm open to trying to learn. Any ideas? I know that's a lot of information at once, so please don't hesitate to ask questions if you have them. Thanks!
Reply With Quote
