I have a report that uses an underlying select query (which is based on a crosstab query).
This report allows the user to generate sales figures for all salespeople within the company for a selected month. The user gets prompted to enter a month (1-12).
For example, we have 100 salespeople in our company and 25 of them had sales for November. When an end user runs the report they are prompted to enter a desired month. In this case, 11. The report then lists ALL 100 salespeople but only the sales figures for the 25 who had sales in November. The other 75 salespeople who had no sales for that month are zeros.
The report works perfectly but what I'm having trouble with is that I want to put "For the month of: <whatever month was selected>". I'm getting an error because the underlying select query returns records for ALL salespeople and thus the "month" field would, in the above example, have either "11" for those with sales activity OR would be null for the other salespeople who didn't have sales that month.
I've tried to use the crosstab query results for the "month" field since those records return only those records for salespeople who had sales for that month and thus its "month" field would have only "11".
In an unbound text box on the report in the ControlSource property I put "=[crosstab query name]![field name]" (without the quotes) and the same as above but with a period instead of an exclaimation point. Both ways gives me errors.
Would anyone have any suggestions as to what I'm doing wrong?
Many TIAs
K
This report allows the user to generate sales figures for all salespeople within the company for a selected month. The user gets prompted to enter a month (1-12).
For example, we have 100 salespeople in our company and 25 of them had sales for November. When an end user runs the report they are prompted to enter a desired month. In this case, 11. The report then lists ALL 100 salespeople but only the sales figures for the 25 who had sales in November. The other 75 salespeople who had no sales for that month are zeros.
The report works perfectly but what I'm having trouble with is that I want to put "For the month of: <whatever month was selected>". I'm getting an error because the underlying select query returns records for ALL salespeople and thus the "month" field would, in the above example, have either "11" for those with sales activity OR would be null for the other salespeople who didn't have sales that month.
I've tried to use the crosstab query results for the "month" field since those records return only those records for salespeople who had sales for that month and thus its "month" field would have only "11".
In an unbound text box on the report in the ControlSource property I put "=[crosstab query name]![field name]" (without the quotes) and the same as above but with a period instead of an exclaimation point. Both ways gives me errors.
Would anyone have any suggestions as to what I'm doing wrong?
Many TIAs
K
