Hi, Ken.
Unfortunately, I have to format it. This particular question is on the
heels
of what I was working on in an earlier post:
To further clarify. Here's my first query qryFacsCasesProduced100M:
SELECT tblFacilitiesCasesProduced.txtFacilityID,
(Format([ProdDate],"mmm""
'""yy")) AS ProdDate1, tblFacilitiesCasesProduced.TotalCasesProduced,
([TotalCasesProduced]/100000) AS Per100M
FROM tblFacilitiesCasesProduced
WHERE
(((tblFacilitiesCasesProduced.txtFacilityID)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR ((([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));
This query returns [ProdDate1] in chronological order. No problems.
It's
then used in qryCompsFacsPerMonthSum100M:
SELECT qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced,
([Per100M]/[SumOfTotalComps1])
AS Per100MSum
FROM qryFacsCasesProduced100M INNER JOIN qryCompsFacsPerMonthSum ON
qryFacsCasesProduced100M.ProdDate1 =
qryCompsFacsPerMonthSum.DateReceived1
GROUP BY qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced,
([Per100M]/[SumOfTotalComps1]);
In this query [ProdDate1] sorts alphabetically. I need it to be
chronological.
--
www.Marzetti.com
:
Don't use Format as an intermediate step:
DateSerial(Year([ProdDate]),Month([ProdDate],1)
--
Ken Snell
<MS ACCESS MVP>
My query has a field:
ProdDate1: (Format([ProdDate],"mmyyyy"))
I'm now trying to convert that to:
DateSerial(Left([ProdDate1],2),Right([ProdDate1],4),1)
This isn't working. For example, the value 122006 is returning as
2/1/179.
What am I doing wrong?
Thanks for your help!