Crosstab Date Range in Reports

[stvlai]

Hi

I am having some problems using Date Range on Crosstab to create a
Reports.

I specify the 'StartDate' & 'EndDate' for the Date Range in the Query
Parameters Table. It prompt me for the Start & End Date. It works fine
in the CrossTab Query.

However, when I try to create a Report using the Report Wizard based
on the CrossTab Query with Date Range, it does not list any of the
Query Fields or Column at all. Just blank! If I remove the Date Range
from the CrossTab Query, then it show all the Query's fields. Strange!

Does anyone have any idea why it is doing this? Any suggestions or
ideas is very much appreciated. Thanks again.

Steven

 

[strive4peace]

Hi Steven,

you might try specifying the data type for the parameters

if you are using Access 2003 or lower -->
from the menu: Query, Parameters

also, define the report using the query without parameters, then, add
the parameters to run it

if you still have a problem, you might try getting the parameters from a
form instead of inputting them

make a form:
Name --> frmReportMenu

textbox control
Name --> Date1

textbox control
Name --> Date2

then, in your query:

Between cDate(forms!frmReportMenu!Date1) and
cDate(forms!frmReportMenu!Date2)


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[stvlai]

Crystal

Thanks a lot for your reply. I have a question about your solution.
When you suggest to insert the Between in the Query,

Between cDate(forms!frmReportMenu!Date1) and
cDate(forms!frmReportMenu!Date2)

Q1) Which Field do I choose on the Field Row? Do I need to use a
field?
Q2) The Between statement, do I insert into the Criteria or Field Row?

Thanks again!

Steven

 

[strive4peace]

Hi Steven,

the field row would have your date field and a criteria row under it
will have the 'Between' statement

the Where clause of the resulting SQL statement, assuming no other
criteria, will be:

WHERE [date_fieldname] BETWEEN cDate(forms!frmReportMenu!Date1) AND
cDate(forms!frmReportMenu!Date2)

the reason that the form references are wrapped in the cDate function is
to convert the text (in the textbox, assuming it is unbound) to a
date/time data type

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*