Filter or Global Variable?

[burcher]

Our company has a program that is funded by the Government, that basically will pay upto $200 towards Health care, $75 towards Dental care, and $75 towards Rx, per year and per Migrant farmworker. The company has a fiscal year from 7/01 to 6/30, which each 7/01 each migrant farmworker is then eligible again for the $$ listed above.
I have a form with a subform. The subform's record source is based on a crosstab query from a table called [Voucher Data Table]. Crosstab's sql follows:
TRANSFORM CCur(Nz(Sum([Voucher Data Table].[Amount Issued]),0)) AS [SumOfAmount Issued]
SELECT [Patient Information Table].pID
FROM [Voucher Data Table] RIGHT JOIN [Patient Information Table] ON [Voucher Data Table].pID = [Patient Information Table].pID
GROUP BY [Voucher Data Table].pID, [Patient Information Table].pID
PIVOT [Voucher Data Table].tID In ("1","2","3");

1,2,3 are medical, dental, and Rx, respectively. I also have a field in the [Voucher Data Table] called [Date Issued] which I could add to the crosstab query with the "Where" criteria.
I would like to have a combo box on the main form that would list the calendar years, and when selected would filter the subform so it only added the [SumOfAmount] for that calendar year.
Would it be easier to use a filter or a global variable (which I have never done). This seemed like such an easy thing but has had me going in circles for the last couple of days. I have reviewed several types of global variables and filter queries, but could not find any that fit this scenerio.
I would of course need to be able to run reports based on this criteria(that's why I thought a global variable would be better than a form filter). Please help, you guys are great at this stuff.

 

[jrbarnett]

Burcher

Instead of using a filter or global variable, the technique described in this FAQ will let you create a form in which you will be able to select items. It does require a bit of VBA coding, but the end result is a very flexible system.

http://www.tek-tips.com/faqs.cfm?spid=702&sfid=3619

John

 

[burcher]

I took a look at

http://www.tek-tips.com/faqs.cfm?spid=702&sfid=3619

and that is a great tool for report flexibility, but I don't see any way to make it work with a subform? See when you open the form, the subform has 3 columns that looks kinda like this:
Pharmacy Dental Medical
Max Benefit $75.00 $75.00 $200.00
Amt Used $10.00 $50.00 $125.00
Amt Remain $65.00 $25.00 $ 75.00

The Max Benefit is in the Patient Data table, Amount used is determined by a crosstab query and the amt Remaining is a calculated field. There also is a button you can push to pull up all the vouchers that make up the amt used. I need to set parameters so that the crosstab query only pulls from the current fiscal year, say 7/01/03 to 6/30/04 and any vouchers entered for this person with a voucher date before 7/01/03 would not be included in the amt used totals. I've tried several things, some from ideas that I got from this forum, but can't get them to work correctly. Some of them that filter correctly, but then causes the crosstab query to cause the subform to disappear if there are not any balances instead of just putting $0.00 in each field (which it does without any date parameters now). So I tried filtering the form without any luck..basically from the lack of know how. That's why I was thinking that a global parameter might be best since there will be several forms and reports that will have to be filtered the same way since they will basically always be working in the current fiscal year, but I need all the prior dated information for the UDS report. Any suggestions?? You guys have solved every question in this forum so I know this problem will be simple for you guys, but it's driving me crazy...