G
Guest
I have a cross tab query using dates as column headers. These dates will change weekly. Is there a way to create a report from this query. Thanks in advance.
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Duane Hookom
This is a reply I gave on this news group 2 days ago. It can work equally
well with a single day rather than month.
Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance if your column headings were months:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the Query|Parameters
Forms!frmA!txtEndDate Date/Time
Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)
This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form.
Mth1 is the previous month etc.
Set your queries Column Headings property to:
Mth0, Mth1, Mth2, Mth3,.., Mth11
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
....
This solution requires no code and will run fairly quickly.
--
Duane Hookom
MS Access MVP
in advance.
well with a single day rather than month.
Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance if your column headings were months:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the Query|Parameters
Forms!frmA!txtEndDate Date/Time
Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)
This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form.
Mth1 is the previous month etc.
Set your queries Column Headings property to:
Mth0, Mth1, Mth2, Mth3,.., Mth11
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
....
This solution requires no code and will run fairly quickly.
--
Duane Hookom
MS Access MVP
change weekly. Is there a way to create a report from this query. Thanksddurman said:I have a cross tab query using dates as column headers. These dates will
in advance.
D
doreen
Where exactly do you define the cloumn headings? In the
query itself or in the report?
query itself or in the report?
-----Original Message-----
This is a reply I gave on this news group 2 days ago. It can work equally
well with a single day rather than month.
Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance if your column headings were months:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the Query|Parameters
Forms!frmA!txtEndDate Date/Time
Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA! txtEndDate)
This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form.
Mth1 is the previous month etc.
Set your queries Column Headings property to:
Mth0, Mth1, Mth2, Mth3,.., Mth11
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
....
This solution requires no code and will run fairly quickly.
--
Duane Hookom
MS Access MVP
headers. These dates willI have a cross tab query using dates as column
change weekly. Is there a way to create a report from this query. Thanks
in advance.
.
D
Duane Hookom
There is a column headings property in the crosstab query.
--
Duane Hookom
MS Access MVP
--
Duane Hookom
MS Access MVP
doreen said:Where exactly do you define the cloumn headings? In the
query itself or in the report?
-----Original Message-----
This is a reply I gave on this news group 2 days ago. It can work equally
well with a single day rather than month.
Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance if your column headings were months:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the Query|Parameters
Forms!frmA!txtEndDate Date/Time
Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA! txtEndDate)
This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form.
Mth1 is the previous month etc.
Set your queries Column Headings property to:
Mth0, Mth1, Mth2, Mth3,.., Mth11
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
....
This solution requires no code and will run fairly quickly.
--
Duane Hookom
MS Access MVP
headers. These dates willI have a cross tab query using dates as column
change weekly. Is there a way to create a report from this query. Thanks
in advance.
.
D
Doreen
It works perfectly!!!
Thank you for all of your advice!
Thank you for all of your advice!
-----Original Message-----
There is a column headings property in the crosstab query.
--
Duane Hookom
MS Access MVP
Where exactly do you define the cloumn headings? In the
query itself or in the report?
It-----Original Message-----
This is a reply I gave on this news group 2 days ago.
can work equallyYouwell with a single day rather than month.
Try not to use "absolute" column headings for dates.
could possibly usein"relative" dates. For instance if your column headings were months:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the Query|Parameters
Forms!frmA!txtEndDate Date/Time
Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA! txtEndDate)
This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form.
Mth1 is the previous month etc.
Set your queries Column Headings property to:
Mth0, Mth1, Mth2, Mth3,.., Mth11
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
....
This solution requires no code and will run fairly quickly.
--
Duane Hookom
MS Access MVP
"ddurman" <[email protected]> wrote
messagenews:41F0A4DE-900D-4C1A-A2A9- (e-mail address removed)...
I have a cross tab query using dates as column headers. These dates will
change weekly. Is there a way to create a report from this query. Thanks
in advance.
.
.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.