Pulling data from a range of dates

Morning all!

Okay so here's the situation... I have employees that have had rate changes during a job and if I edit the information and run a report for a previous week ending date the payrate is wrong (obviously because I changed it) and what I need to be able to do is list the person's name and rate i.e.
ST RATE OT RATE
John Smith 25.00 37.50 This is before the rate change

John Smith 30.00 45.00 After rate change

What I was thinking is that I could place a couple columns with a range of dates and then if the week ending date falls within that window it'll pull the current rate as opposed to the previous rate. I've tried a few things and it doesn't seem to want to recognize the dates and it especially doesn't like an open date. Can someone help me out on this? It's kicking my butt...

Thanks,

Bill Sublette

Here is a quick example of what I need. There will be additional dates that fall within the week ending date and hours worked during that week but didn't that that was needed for the example... If it can pull according to the dates at that rate it'll pull all the data right?

Thanks Again,

Bill Sublette