Date Limit in Crosstab from Union query

[Molasses26]

Hello! I have a union query that links together multiple tables and it works
like a charm, however when I try to run a crosstab query based on it and
limit it to a specified date range it does not seem to recognize that they
ARE dates so I still get everything. Some of the dates are entered as date
and time and some of them are just the date, in case that matters.

Any help would be greatly appreciated!

Union Query:
SELECT "AMIEst" as Source, [Site] as IDNo, Upld_Dt, Review_Dt,Analyst from
[tblReport_AMIEstimatedRds]
UNION SELECT "E32Min", [SAA],Upld_Dt, Review_Dt, Analyst from
[tblReport_E32MinBills]
UNION SELECT "HighkWh", [Site],Upld_Dt, Review_Dt,Analyst from
[tblReport_HighkWh]
UNION SELECT "MtrTestCC",[Site],Upld_Dt, Review_Dt, Analyst from
[tblReport_MeterTest_CC]
UNION SELECT "MtrTest3Pct",[Site],Upld_Dt, Review_Dt, Analyst from
[tblReport_MeterTest_gt3Pct]
UNION SELECT "ResHighkW", [Site],Upld_Dt, Review_Dt,Analyst from
[tblReport_ResAcctsHighkW]
UNION SELECT "SAAsNotBlld",[Site],Upld_Dt, Review_Dt,Analyst from
[tblReport_SAAsNotBilled]
UNION SELECT "Audit/Report",[ItemNbr],EntryDate,[Review Date],Auditor from
[tblData] WHERE (ReviewType=2 and Auditor not in( "Z00000","X00000"))
UNION SELECT "Random/Coach",[ItemNbr],EntryDate,[Review Date],Auditor from
[tblData] WHERE (ReviewType<>2 and Auditor not in( "Z00000","X00000"))
ORDER BY Review_Dt;

Crosstab query:
TRANSFORM Count(qry_AnalystReportStats.IDNo) AS CountOfIDNo
SELECT qry_AnalystReportStats.Analyst, qry_AnalystReportStats.Review_Dt
FROM qry_AnalystReportStats
WHERE (((qry_AnalystReportStats.Review_Dt)>#08/01/2009#))
GROUP BY qry_AnalystReportStats.Analyst, qry_AnalystReportStats.Review_Dt
PIVOT qry_AnalystReportStats.Source;

 

[KARL DEWEY]

Try this --
WHERE ((CVDate(qry_AnalystReportStats.Review_Dt)>#08/01/2009#))

 

[Molasses26]

PERFECT!
Thank you so much!

Try this --
WHERE ((CVDate(qry_AnalystReportStats.Review_Dt)>#08/01/2009#))

--
Build a little, test a little.

Hello! I have a union query that links together multiple tables and it works
like a charm, however when I try to run a crosstab query based on it and
limit it to a specified date range it does not seem to recognize that they
ARE dates so I still get everything. Some of the dates are entered as date
and time and some of them are just the date, in case that matters.

Any help would be greatly appreciated!

Union Query:
SELECT "AMIEst" as Source, [Site] as IDNo, Upld_Dt, Review_Dt,Analyst from
[tblReport_AMIEstimatedRds]
UNION SELECT "E32Min", [SAA],Upld_Dt, Review_Dt, Analyst from
[tblReport_E32MinBills]
UNION SELECT "HighkWh", [Site],Upld_Dt, Review_Dt,Analyst from
[tblReport_HighkWh]
UNION SELECT "MtrTestCC",[Site],Upld_Dt, Review_Dt, Analyst from
[tblReport_MeterTest_CC]
UNION SELECT "MtrTest3Pct",[Site],Upld_Dt, Review_Dt, Analyst from
[tblReport_MeterTest_gt3Pct]
UNION SELECT "ResHighkW", [Site],Upld_Dt, Review_Dt,Analyst from
[tblReport_ResAcctsHighkW]
UNION SELECT "SAAsNotBlld",[Site],Upld_Dt, Review_Dt,Analyst from
[tblReport_SAAsNotBilled]
UNION SELECT "Audit/Report",[ItemNbr],EntryDate,[Review Date],Auditor from
[tblData] WHERE (ReviewType=2 and Auditor not in( "Z00000","X00000"))
UNION SELECT "Random/Coach",[ItemNbr],EntryDate,[Review Date],Auditor from
[tblData] WHERE (ReviewType<>2 and Auditor not in( "Z00000","X00000"))
ORDER BY Review_Dt;

Crosstab query:
TRANSFORM Count(qry_AnalystReportStats.IDNo) AS CountOfIDNo
SELECT qry_AnalystReportStats.Analyst, qry_AnalystReportStats.Review_Dt
FROM qry_AnalystReportStats
WHERE (((qry_AnalystReportStats.Review_Dt)>#08/01/2009#))
GROUP BY qry_AnalystReportStats.Analyst, qry_AnalystReportStats.Review_Dt
PIVOT qry_AnalystReportStats.Source;