Hi, need your help again. I am trying to create a button on my form which will open a filtered report. I have already created the report and totals have been created for individual customers (who have their unique ID). The total is in my ID Footer section (report design view). I want the report to permanently show the customers whose totals are 3600 (and they are eligible for a surprise gift). The rest will be filtered out until they meet the criteria. I have tried the following but there is something wrong.
Private Sub Command26_Click()
DoCmd.OpenReport "EliParameterReport", acViewPreview, "[Sum_Of_Purchases] = " & 3600
"Sum_Of_Purchases" is the Total field
Like I said I am still a novice. Thank you in advance
Thank you for the quick reply. I live in the Eastern Hemisphere. When I started this thread, it was 4.30 a.m. I just went to sleep after that.
OK, I tried what you posted and a parameter box pops up and asks for Sum_Of_Purchases. Whether I enter anything or not, the report displays 0 results. It could be one of two problems (I think).
1.) Is it because the control is an unbound text box?
2.) Or is it because the control in situated in the ID Footer section (grouped
by in the report).
When I change to a bound text box in the detail section, it works! (Unfortunately that is not what I want) The query that I base the report on is made up of 3 queries. The end result is working fine when I open the report on its own.
Yes. Thank you. I went back to the query, clicked on the Totals on the ribbon,
Grouped By ID to Count and Purchases to Sum, created a Total in the next column of the query in Design View and its working.
Hey Paul, you always manage to put me in the right direction
Thank you again. Simple and logical. That's the best
Hi again, I realized one thing. When you have too many columns in a Group By (Totals) Query, it goes haywire. I finally settled for only four columns, namely
ID - Group By
SubID - Group By
NAME - Group By
PURCHASES - Sum (which once you sum, converts to SumOfPURCHASES)
Then I created a button
with the following On Click Event Procedure
DoCmd.OpenReport "EliReport", acViewPreview, , "[SumOfPURCHASES]=3600"
EliReport is the Grouped By ID Summary Report with Totals. The report is created from the query.
Once you click the button, it only shows all Purchases Totals (Grouped By ID) which equal to 3600
I think the trick is to have as few fields as possible in the Query