Problem with Calculated Date

[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.

 

[Wolfgang Kais]

Hello knitter.

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 ofA
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.

So that crosstab query based on a table works fine, right?
And where's the query that doesn't work?

 

[knitter via AccessMonster.com]

Hello knitter.

Hello,
I have a query that calculates a year's worth of "Due" dates

[quoted text clipped - 26 lines]

table.
Thanks to anyone who can help.

So that crosstab query based on a table works fine, right?
And where's the query that doesn't work?

It's the same query except for the source. I'm just trying to use the same
query that makes the table.

 

[Wolfgang Kais]

Hello knitter.

I have a query that calculates a year's worth of "Due" dates
[quoted text clipped - 26 lines]
table.
Thanks to anyone who can help.

So that crosstab query based on a table works fine, right?
And where's the query that doesn't work?

It's the same query except for the source. I'm just trying to use the same
query that makes the table.

....which we still don't know, therefore, it's hard to help you.

 

[knitter via AccessMonster.com]

Hello knitter.

I have a query that calculates a year's worth of "Due" dates
[quoted text clipped - 26 lines]
table.
Thanks to anyone who can help.
So that crosstab query based on a table works fine, right?
And where's the query that doesn't work?

It's the same query except for the source. I'm just trying to use the same
query that makes the table.

...which we still don't know, therefore, it's hard to help you.

Sorry, I'm confused as to what you're looking for. I posted the query I'm
using. This query works fine running off of a table. I want to run it against
a query with the exact same fields. The only difference is that DueDate1
would be a calculated field, and that's where I'm getting my mismatch error.

 

[John Spencer]

Well, it is possible that the source query is not building a date field,
but rather a string that looks like a date. However, we don't know and
have no possible way of knowing. You have not posted the source query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello knitter.

I have a query that calculates a year's worth of "Due" dates
[quoted text clipped - 26 lines]
table.
Thanks to anyone who can help.
So that crosstab query based on a table works fine, right?
And where's the query that doesn't work?
It's the same query except for the source. I'm just trying to use the same
query that makes the table.

...which we still don't know, therefore, it's hard to help you.

Sorry, I'm confused as to what you're looking for. I posted the query I'm
using. This query works fine running off of a table. I want to run it against
a query with the exact same fields. The only difference is that DueDate1
would be a calculated field, and that's where I'm getting my mismatch error.

 

[John W. Vinson]

Sorry, I'm confused as to what you're looking for. I posted the query I'm
using. This query works fine running off of a table. I want to run it against
a query with the exact same fields. The only difference is that DueDate1
would be a calculated field, and that's where I'm getting my mismatch error.

Please open both queries - the calling one and the called one - in SQL view
and post the SQL text here.

John W. Vinson [MVP]

 

[Wolfgang Kais]

Hello knitter.

I have a query that calculates a year's worth of "Due" dates
[quoted text clipped - 26 lines]
table.
Thanks to anyone who can help.
So that crosstab query based on a table works fine, right?
And where's the query that doesn't work?
It's the same query except for the source. I'm just trying to
use the same query that makes the table.

...which we still don't know, therefore, it's hard to help you.

Sorry, I'm confused as to what you're looking for. I posted the
query I'm using. This query works fine running off of a table.
I want to run it against a query with the exact same fields.
The only difference is that DueDate1 would be a calculated field,
and that's where I'm getting my mismatch error.

Did you use the query name instead of the table name, or did you
use a select statement? How did you calculate DueDate1?
If you posted the sql of the "make table" query, we eventually
could find another way of getting the desired result.