Can't separate project data based on date range

[jason.cyr13]

I have a database where the main data is timecard information and is inserted into a table every week. I have a second table that has project specific data, including project number (linking fields) and a checkbox for active or not.

tblProject (sample data)
recordID   projectName   projectNumber   projectTask   submissionType   numbersheets   targetAveDR   targetAvgDS startweek    endweek     active
1             Project A        1234567890      000.111        Prelim                   26                       7.5              4.5      10/18/2013   12/1/2013   N
2             Project B         2345678901     000.111         Prelim                  20                       7.5               4.5      11/15/2013   1/3/2014     Y
3             Project A        1234567890       000.111        Final                    26                       3.5                2        1/3/2014       3/1/2014   Y

tblTimecard (sample data)
recordID  Wkending    role  employee    project                         task      sat sun mon tue wed thu fri   total
1           10/25/2013   DR   EmployeeA  1234567890 - Project A  000.111   0    0   7.5  7.5 7.5  7.5 7.5 37.5
2           11/1/2013     DR   EmployeeA  1234567890 - Project A  000.111   0    0   7.5  7.5 7.5  7.5 7.5 37.5
3           1/3/2014       DR  EmployeeA   1234567980 - Project A  000.111   0    0  7.5  7.5  7.5  7.5 7.5 37.5
4           1/10/2014      DR  EmployeeA   1234567980 - Project A  000.111   0    0  7.5  7.5  7.5  7.5 7.5 37.5

I have a field in my query that truncates the project down to just the number portion. The rest of the text is added into the timecard system and isn't required if I manually enter the project info. Because of how the accounting for a project works, there is only one number for the project that lasts its entire lifecycle, but there can be a couple of submissions. What I want to do is pull all of the timecard data for the second submission, without including any information from the first submission.

As an example, Project A was active for the prelim submission from 10/18/2013-12/1/2013. The query works fine to pull this info, as long as it's the only project. Once the second submission to Project A starts, say 1/3/2014-3/1/2014, the active flag is flipped off for the first submission. In my query, I only want to see timecard entries for part 2 of Project A on a date that's equal to the start date of the second submission. I also don't want to modify the timecard data, as that defeats the purpose of having a DB do the heavy lifting.

The reason for the separation is the targets for each submission vary, and I need to track the hours charged for each submission. I don't want to have a separate report for each type, but since I can't filter the dates properly, that doesn't matter. I have even created a calculated field to calc the number of days since 1/1/1900 and filter rows >= to this number. That didn't work either.

Any help would be greatly appreciated.

I already shave my head, so I can't pull out any hair. This is probably leading to higher than normal frustration levels.

Jason

[ItsMe]

Without going into detail about your post or your data structure, or how you retrieve the data....

My payroll modules use unbound textboxes to get date parameters. The user input date parameter adds criteria to the rowsource for a combobox. Additional combobox criteria are Booleans to determine if a given record is eligible to be in the "Payroll" stage.

From there I use additional, dependent combos and subforms. The subforms cascade their dependency from the combos, the final subform is dependent on previous subforms. The previous subforms are provided for user edits and the final subform is a summary (not for edits). All RowSources and Recordsources are dynamically updated using VBA and SQL strings in AfterUpdate events.