I have a paremeter query which gives me correct information - one or more records, per month, per contract #. When I run the query and enter "1" for the month field parameter (not shown below, but meaning I want all records which have a DisbMonth of 1, or a RecMonth of 1) I'll get the following correct information. I put dots between the columns because the preview looked unreadable with a bunch of spaces:
contract....DisbMonth...RecMonth.......CYNetDisb
MD060018004.......1..........1.........$12,424.00
MD060018004.......2..........1.........$14,618.00
If I enter "2" for the month field, I'll get the following correct records:
contract....DisbMonth...RecMonth....CYNetDisb
MD060018004.......1..........1.........$12,424.00
MD060018004.......2..........1.........$14,618.00
MD060018004.......2..........2.........$29,236.00
When I run a report based on this query (and thus, prompts me for the month I'm interested in) I get $27,042 when I enter "1" for the month, but I only want dollars for this field if the DisbMonth is less than or equal to the month I enter. By the end of our fiscal year, there could be 12 months (or records) for each contract.
I've tried a bunch of different formulas on the report for the CYNetDisb field. I can't get it to work, without messing up something else. I think I should have something like, in english, give me the sum of CYNetDisb when the DisbMonth is less than or equal to the month I am prompted for, by the parameter query. I think the following formula on the report should work, but it doesn't.
=IIf([DisbMonth]<=[MonthSelected],Sum([CYNetDisb]),0)
If I enter "1", for the MonthSelected, I get dollars that are in DisbMonth 2 and 1, instead of just DisbMonth 1.
The field MonthSelected is the field I've defined in the query this report uses as "MonthSelected: [enter 1 or 2 digit fiscal month]." In the query, for criteria, I have DisbMonth = the parameter or RecMonth = the parameter, and the query is correct and gives me the correct records. The report might work correctly if the user is prompted for the DisbMonth and the RecMonth, but I want the user to enter the month one time instead of two times.
Anyone have any ideas as how to do this?
contract....DisbMonth...RecMonth.......CYNetDisb
MD060018004.......1..........1.........$12,424.00
MD060018004.......2..........1.........$14,618.00
If I enter "2" for the month field, I'll get the following correct records:
contract....DisbMonth...RecMonth....CYNetDisb
MD060018004.......1..........1.........$12,424.00
MD060018004.......2..........1.........$14,618.00
MD060018004.......2..........2.........$29,236.00
When I run a report based on this query (and thus, prompts me for the month I'm interested in) I get $27,042 when I enter "1" for the month, but I only want dollars for this field if the DisbMonth is less than or equal to the month I enter. By the end of our fiscal year, there could be 12 months (or records) for each contract.
I've tried a bunch of different formulas on the report for the CYNetDisb field. I can't get it to work, without messing up something else. I think I should have something like, in english, give me the sum of CYNetDisb when the DisbMonth is less than or equal to the month I am prompted for, by the parameter query. I think the following formula on the report should work, but it doesn't.
=IIf([DisbMonth]<=[MonthSelected],Sum([CYNetDisb]),0)
If I enter "1", for the MonthSelected, I get dollars that are in DisbMonth 2 and 1, instead of just DisbMonth 1.
The field MonthSelected is the field I've defined in the query this report uses as "MonthSelected: [enter 1 or 2 digit fiscal month]." In the query, for criteria, I have DisbMonth = the parameter or RecMonth = the parameter, and the query is correct and gives me the correct records. The report might work correctly if the user is prompted for the DisbMonth and the RecMonth, but I want the user to enter the month one time instead of two times.
Anyone have any ideas as how to do this?
