Filter Records based on Date Range
I’m trying to improve my Invoicing workflow. I work on a contract basis and invoice every 2 weeks on Thursdays.
I currently have a Table called "Hours’ with fields for the {Date}, {Hours Worked} (how many hours I worked that date), and a {Invoice Code}(Links to the Invoice Table). I have another table called {Invoice}. It has fields {Invoice Code}, {Start Date}, {End Date} and {Hours} linked to from the {Hours} table.
I currently have to manually link the {Hours Worked} field in the {Hours} table to the appropriate record in the {Invoice} table.
What I would like to do is to link each record in the {Hours} table, to the record in the {Invoice} Table automatically if if it falls in between the {Start Date} and {End Date} in the {Invoice} table.
I have figured out to to make a formula to determine if a date in in a specific range but not how to search all the record in a table to determine which ones should be linked to appropriately.
Any help would be much appreciated.
Cheers
Evan
