Crosstab Query for Report 
Author Message
 Crosstab Query for Report

I have a crosstab query that serves as the basis of a report.  The report
lists sales by customer, division and type for a 6 month period and has
footers that sum by type for division and customer.  The column heading for
the crosstab query is the sales month and is written as
    Expr1: Format([OrdDate],"mmm").

The report works great - as long as month is specified directly in the
footer sum as:
    =Sum(IIf([type_id]=2,[Mar],0))

The problem comes when I want to try to change the month.  The report should
print for any 6 month period, so if a user specifies Feb as the starting
month, it should list all sales from Feb up to and including Jul.  It does
this in the detail section but not in the footer.  The main sticking point
is that I can't figure out how to dynamically change the sum/iif statment in
the footer. IOW I need to change [MAR] to [APR] or some other value
depending on the range the user specified.

One potential solution I tried was to assign each of the months in the range
to a textbox (e.g., txtDate1, txtDate2..) on the report and then reference
that control in my Sum/Iff statement. Something like:
    =Sum(IIf([type_id]=2,[txtDate1],0))

However this results in a 'Enter Parameter' dialog box for the control when
the report is run.  At that point no mater what value I enter in the dialog
box ([APR], APR, "APR", ["APR"], 4, #4/1/99#, 4/1/99, "4/1/99"). I get a
datatype mismatch warning and the value is not accepted.

I also tried changing the control source of the textbox controls in the
footer programatically.  This actually works with an individual text box but
I run into problems trying to loop through multiple textboxes.  Apparently
VBA does not support control arrays, or at least I could not find anything
about them in the ADH, Mastering Access or the online help.

So now I am stumped.   The root of the problem appears to be the datatype
issue. I do not understand why the report will take [APR] in a Sum/Iff
statement directly but not when passed as a parameter.

If someone could point me in the right direction, I would be very grateful.

Dave
---
David R. Frick & Company, CPA

http://www.*-*-*.com/



Fri, 27 Jul 2001 03:00:00 GMT  
 Crosstab Query for Report
Hi,

For what it worths, (and probably less than $0.001 cent ...  holy guacamole,
just make it Canadian$), try to use an intermediate query that will just
ALIAS your fields:

SELECT  Month1 As F1, Month2 As F2, ............ FROM yourCrosstab;

and use the CONSTANT fields name, F1, F2, .......... in the report, basing
its recordsource to the aliasing-query.

Hope it may help,
Vanderghast, Access MVP.

Quote:

>I have a crosstab query that serves as the basis of a report.  The report
>lists sales by customer, division and type for a 6 month period and has
>footers that sum by type for division and customer.  The column heading for
>the crosstab query is the sales month and is written as
>    Expr1: Format([OrdDate],"mmm").

>The report works great - as long as month is specified directly in the
>footer sum as:
>    =Sum(IIf([type_id]=2,[Mar],0))

>The problem comes when I want to try to change the month.  The report
should
>print for any 6 month period, so if a user specifies Feb as the starting
>month, it should list all sales from Feb up to and including Jul.  It does
>this in the detail section but not in the footer.  The main sticking point
>is that I can't figure out how to dynamically change the sum/iif statment
in
>the footer. IOW I need to change [MAR] to [APR] or some other value
>depending on the range the user specified.

>One potential solution I tried was to assign each of the months in the
range
>to a textbox (e.g., txtDate1, txtDate2..) on the report and then reference
>that control in my Sum/Iff statement. Something like:
>    =Sum(IIf([type_id]=2,[txtDate1],0))

>However this results in a 'Enter Parameter' dialog box for the control when
>the report is run.  At that point no mater what value I enter in the dialog
>box ([APR], APR, "APR", ["APR"], 4, #4/1/99#, 4/1/99, "4/1/99"). I get a
>datatype mismatch warning and the value is not accepted.

>I also tried changing the control source of the textbox controls in the
>footer programatically.  This actually works with an individual text box
but
>I run into problems trying to loop through multiple textboxes.  Apparently
>VBA does not support control arrays, or at least I could not find anything
>about them in the ADH, Mastering Access or the online help.

>So now I am stumped.   The root of the problem appears to be the datatype
>issue. I do not understand why the report will take [APR] in a Sum/Iff
>statement directly but not when passed as a parameter.

>If someone could point me in the right direction, I would be very grateful.

>Dave
>---
>David R. Frick & Company, CPA

>http://www.frick-cpa.com



Sat, 28 Jul 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Crosstab Query/Conversion/Report Generation...

2. report based on a crosstab query

3. report based on a crosstab query

4. dynamic report formatting from crosstab queries??

5. query regarding Crosstab report

6. multiple queries into a crosstab query in VB

7. multiple queries into a crosstab query in VB

8. Crystal Report : CrossTab Report

9. VBA for crosstab query

10. Opening a CrossTab query via QueryDef.OpenRecordset

11. Crosstab Query

12. Sort or limit row displayed in a crosstab query

 

 
Powered by phpBB® Forum Software