Find Variables closest to specified Date

[crimedog]

Okay - almost finished (thanks to this forum)
I have Table of Sales
Table Of Reps
Table of Variables (Rep's Quota may change in a year, they may change Position etc... this table has [date of change][Quota][Position][Salary])

My Quarterly report : form chooses [Rep] [Month] [Quarter] - Then report shows the Sales Grouped and Subtotaled By [Month]
I would like to show in the Group Header the [Quota] for the [Month]

I Query the Variables Table by Rep - so now I have a list of Dates Quota Position Salary (every Rep will have at least one record) - And There should only be one record per month - Most Reps will only have changes every January 1st (raises & quota changes) - some may get promoted during the year

So - the expression in the Text Box in the Group Header? DSum("[Quota]","[VariableQuery]","[DateOfChange]=??

In the Header I have the Month Value and in the Page Header I have the Year Value ( call them TxtMonth & TxtYear) (except there are 3 TxtMonths (1,2,3 or 4,5,6 etc...)


(so I want the Date of change that is Closest BEFORE or EQUAL to [Year]&[Month] without being over

Last example: If rep got new Quota 1,1,2013 and promoted 3,1,2013 (with a new quota), and changed position again in 6,1,2013 with ANOTHER Quota
If I am looking at Quarter 1 of 2013 - Group Header for January (1) AND Feb (2) should show Quota for 1,1,2013
AND group Header for Mar (3) should both show the Quota for 3,1,2013

[rpeare]

any chance of a sample database, the approach might differ radically depending on your structure.