K
knitter via AccessMonster.com
Hello,
I have a query that calculates a year's worth of "Due" dates based on
frequency which is working fine. I'd like to be able to to select out in any
of those "Due" fields anything showing up in 12 months. If I use the
calculating query to make a table, I can get the results I need. But if I try
to use the same criteria on the calculating query itself, I get a data
mismatch error.
I'd really rather work off the calculating query vs. having to create this
table over and over.
Here's the SQL that works on the table:
TRANSFORM Count([tblFrequency for Updates].Company) AS [CountOfCompany #]
SELECT [tblFrequency for Updates].State, Count([tblFrequency for Updates].
Company) AS [Total Of Company #]
FROM [tblFrequency for Updates]
WHERE ((([tblFrequency for Updates].DueDate1)<=DateAdd("m",12,Date())))
GROUP BY [tblFrequency for Updates].State
PIVOT Format([duedate1],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul",
"Aug","Sep","Oct","Nov","Dec");
DueDate1 in the table is Date/Time
I've tried using DateValue, CDate, CVDate and all other kinds of date
configurations that I could find here. I have no nulls and the calculated
dates look fine. I'm just not understanding why I get an error using the
query that creates the table but not when I use the table.
Thanks to anyone who can help.
I have a query that calculates a year's worth of "Due" dates based on
frequency which is working fine. I'd like to be able to to select out in any
of those "Due" fields anything showing up in 12 months. If I use the
calculating query to make a table, I can get the results I need. But if I try
to use the same criteria on the calculating query itself, I get a data
mismatch error.
I'd really rather work off the calculating query vs. having to create this
table over and over.
Here's the SQL that works on the table:
TRANSFORM Count([tblFrequency for Updates].Company) AS [CountOfCompany #]
SELECT [tblFrequency for Updates].State, Count([tblFrequency for Updates].
Company) AS [Total Of Company #]
FROM [tblFrequency for Updates]
WHERE ((([tblFrequency for Updates].DueDate1)<=DateAdd("m",12,Date())))
GROUP BY [tblFrequency for Updates].State
PIVOT Format([duedate1],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul",
"Aug","Sep","Oct","Nov","Dec");
DueDate1 in the table is Date/Time
I've tried using DateValue, CDate, CVDate and all other kinds of date
configurations that I could find here. I have no nulls and the calculated
dates look fine. I'm just not understanding why I get an error using the
query that creates the table but not when I use the table.
Thanks to anyone who can help.