Why is report not in date order

[tezread]

I have a report whose query is

PARAMETERS [Forms]![frmReport]![startdate] DateTime, [Forms]![frmReport]![enddate] DateTime;
TRANSFORM Nz(Count(qryEpisode.EpisodeID),0) AS CountOfID
SELECT Format([Date_Referred],"yyyy/mmm") AS [Date]
FROM qryEpisode
WHERE (((qryEpisode.Date_Referred) Between [Forms]![frmReport]![startdate] And [Forms]![frmReport]![enddate]))
GROUP BY Format([Date_Referred],"yyyy/mmm"), Format([Date_Referred],"yyyy/mm")
ORDER BY Format([Date_Referred],"yyyy/mm")
PIVOT qryEpisode.Outcomereport In ("GP","Cardiology","Cardioversion","No outcome");

run the query and the datesa are in order but run the report and the date appears in alphabetical order?

 

[BBQ Kittens]

I assume when you say 'the date appears in alphabetical order' you mean that its sorting on another field since a date can not be alphabetized. For the report, try under report properties to set the order by 'date'. Even if a query reflects the ascending order you prefer, it may not reflect on a form or report, thus the use or order by is necessary.

 

[spikepl]

You have formatted your dates into strings, so the poor thing does what you asked for

If you remove the Format from the Order BY and sort by Date_referred then you might get what you want, unless the report ignores it, (as they habitually do)

 

[tezread]

You have formatted your dates into strings, so the poor thing does what you asked for

If you remove the Format from the Order BY and sort by Date_referred then you might get what you want, unless the report ignores it, (as they habitually do)

this is a cross tab query though so If I remove the format from ORDER by and sort by date referred, all the records show. I want them group by month

Date is my column head. I cannot add column headings in the properties box either because the date could have any start or end date

 

[vbaInet]

Like spikepl mentioned, remove the Format() function.

Then group in your report.

 

[vbaInet]

Wait! You need the group in the crosstab to get the values you want. So don't remove the Format() just yet.

Can we see a screenshot of the report.

 

[spikepl]

Removing Format from the Order By surely ought to do the trick - it does not affect grouping, but just the display order.

 

[vbaInet]

Removing Format from the Order By surely ought to do the trick - it does not affect grouping, but just the display order.

Right you are! I only had one glimpse at the query and thought I saw the Date being used as the Value field.

 

[Taruz]

Hi..:

You can sort with code.. on open is report event..:

orderby= "year([date]),month([date])"

or try same structure in query..:

PARAMETERS [Forms]![frmReport]![startdate] DateTime, [Forms]![frmReport]![enddate] DateTime;
TRANSFORM Nz(Count(qryEpisode.EpisodeID),0) AS CountOfID
SELECT Format([Date_Referred],"yyyy/mmm") AS [Date]
FROM qryEpisode
WHERE (((qryEpisode.Date_Referred) Between [Forms]![frmReport]![startdate] And [Forms]![frmReport]![enddate]))
GROUP BY year([Date_Referred]), month([Date_Referred]),Format([Date_Referred],"yyyy/mmm")
ORDER BY year([Date_Referred]),month([Date_Referred])
PIVOT qryEpisode.Outcomereport In ("GP","Cardiology","Cardioversion","No outcome");

 

[tezread]

Wait! You need the group in the crosstab to get the values you want. So don't remove the Format() just yet.

Can we see a screenshot of the report.

Hi

please see attached

 

[tezread]

Hi..:

You can sort with code.. on open is report event..:

orderby= "year([date]),month([date])"

or try same structure in query..:

PARAMETERS [Forms]![frmReport]![startdate] DateTime, [Forms]![frmReport]![enddate] DateTime;
TRANSFORM Nz(Count(qryEpisode.EpisodeID),0) AS CountOfID
SELECT Format([Date_Referred],"yyyy/mmm") AS [Date]
FROM qryEpisode
WHERE (((qryEpisode.Date_Referred) Between [Forms]![frmReport]![startdate] And [Forms]![frmReport]![enddate]))
GROUP BY year([Date_Referred]), month([Date_Referred]),Format([Date_Referred],"yyyy/mmm")
ORDER BY year([Date_Referred]),month([Date_Referred])
PIVOT qryEpisode.Outcomereport In ("GP","Cardiology","Cardioversion","No outcome");

this works

 

[Khartoum]

Hi, sorry to butt into this but i have the same problem, my query is as below and returns in calendar month order as it should but my report returns alphabetical any thoughts:


SELECT Format([tblsales].[orderdate],'mmm') AS [Month], Sum(tblsales.VAT) AS SumOfVAT, Sum([quantity]*[tblproducts.productprice]+[vat]) AS total, Sum(tblsales.Discount) AS SumOfDiscount, Sum(tblsales.[Shipping&Delivery]) AS [SumOfShipping&Delivery]
FROM tblProducts INNER JOIN tblsales ON tblProducts.ProductCode=tblsales.ProductName
WHERE (((tblsales.OrderDate) Between [Forms]![frmreports]![datefrom] And [Forms]![frmreports]![dateto]))
GROUP BY Format([tblsales].[orderdate],'mmm'), tblsales.ProductName, tblsales.Quantity, tblProducts.ProductPrice, Format([tblsales].[orderdate],'mm')
ORDER BY Format([tblsales].[orderdate],'mm');