Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance: 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|Parameter 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.
Your crosstab might look like:
TRANSFORM Min(tbloutputasset.[Short Code]) AS [MinOfShort Code]
SELECT tbloutputasset.[Contractor Name], tbloutputasset.[Site Name],
tbloutputasset.[Asset Type], tbloutputasset.[Asset Number]
FROM tbloutputasset
GROUP BY tbloutputasset.[Contractor Name], tbloutputasset.[Site Name],
tbloutputasset.[Asset Type], tbloutputasset.[Asset Number]
PIVOT DateDiff("m", [Start Date], Forms!frmA!txtEndDate) In
("Mth1", "Mth2", "Mth3",.., "Mth11");
--
Duane Hookom
MS Access MVP
--
Little pete said:
Thanks this has worked.
However is their an easier way for this, the reports I will run will be a
range of different months/years and I dont want to have months/dates between
jan03 - jan05.
Duane Hookom said:
Your Column Headings property would need to include the year. "Jan 04", "Feb
04",...
--
Duane Hookom
MS Access MVP
--
Little pete said:
The error has gone away but no data is showing in the table. Here is
the
SQL
language for the query. Im sure the prob is with the "Jan", "Feb" - no yy
fields are showing.
Again thanks for the help in advance, pete
TRANSFORM Min(tbloutputasset.[Short Code]) AS [MinOfShort Code]
SELECT tbloutputasset.[Contractor Name], tbloutputasset.[Site Name],
tbloutputasset.[Asset Type], tbloutputasset.[Asset Number]
FROM tbloutputasset
GROUP BY tbloutputasset.[Contractor Name], tbloutputasset.[Site Name],
tbloutputasset.[Asset Type], tbloutputasset.[Asset Number]
PIVOT Format([Start Date],"mmm yy") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
:
Try Expr3: Format([Start Date],"mmm yy")
Hope this does it.
Fons
-----Original Message-----
I have created a query/report using the following;
Expr3: Format([Start Date],"mmm")
This seperates my data into months. I now need to
include which year, ie
Dec 04, Jan 05.
I thought the following might do the trick but no;
Expr3: Format([Start Date],"mmm", "yy")
Thanks in advance
.
