Startdate Enddate criteria error message

[Tyler at Creme]

I am using Access 2007. I have a query parameter that is "Between
[Startdate] and [Enddate]" It works fine with date in 2008, but dates in
2009 for either Start or End date give me the following: "This expression is
typed incorrectly, or it is too complex to be evaluated. For example, a
nuneric expression may contain too many complicated elements. Try simplifying
the expression by assigning pats of the expression to variables. "

How can I get 2009 dates to work as my query criteria?

 

[John W. Vinson]

I am using Access 2007. I have a query parameter that is "Between
[Startdate] and [Enddate]" It works fine with date in 2008, but dates in
2009 for either Start or End date give me the following: "This expression is
typed incorrectly, or it is too complex to be evaluated. For example, a
nuneric expression may contain too many complicated elements. Try simplifying
the expression by assigning pats of the expression to variables. "

How can I get 2009 dates to work as my query criteria?

Sometimes dates aren't perceived as dates! You can force them to be so:
Open the query in design view. Right click the grey background of the tables,
and select Parameters.

Copy and paste your parameters - [Startdate] and [Enddate] - into the left
column, and select date/time from the dropdown in the right column.

This should get rid of the error.

 

[Tyler at Creme]

John,
That did allow me to put in 2009 dates in two ungrouped queries, but I still
get the same error message on reports and queries that group (and also total
and average) by month. Is there something wrong with my averaging or summing
calculated fields? The grouping of months was done through a query or
reports wizards, so I am not familiar with the expressions it created.
Thanks,
Tyler
--
Tyler at Creme
New Access User

I am using Access 2007. I have a query parameter that is "Between
[Startdate] and [Enddate]" It works fine with date in 2008, but dates in
2009 for either Start or End date give me the following: "This expression is
typed incorrectly, or it is too complex to be evaluated. For example, a
nuneric expression may contain too many complicated elements. Try simplifying
the expression by assigning pats of the expression to variables. "

How can I get 2009 dates to work as my query criteria?

Sometimes dates aren't perceived as dates! You can force them to be so:
Open the query in design view. Right click the grey background of the tables,
and select Parameters.

Copy and paste your parameters - [Startdate] and [Enddate] - into the left
column, and select date/time from the dropdown in the right column.

This should get rid of the error.

 

[John W. Vinson]

John,
That did allow me to put in 2009 dates in two ungrouped queries, but I still
get the same error message on reports and queries that group (and also total
and average) by month. Is there something wrong with my averaging or summing
calculated fields? The grouping of months was done through a query or
reports wizards, so I am not familiar with the expressions it created.
Thanks,
Tyler

I'm not either... since I can't see them from here <g>!

Please open the offending query in SQL view and post the SQL text here.

 

[Tyler at Creme]

John,
Here is the SQL view of the offending query:

SELECT DISTINCTROW Weekly_Data_Query.School_Num,
Weekly_Data_Query.School_Name, Format$([Weekly_Data_Query].[Week_Ended],'mmmm
yyyy') AS [Week_Ended By Month], Sum(Weekly_Data_Query.Inquiries) AS [Sum Of
Inquiries], Round(Avg(Weekly_Data_Query.[Inquiries])) AS [Avg Of Inquiries],
Sum(Weekly_Data_Query.Tours) AS [Sum Of Tours],
Round(Avg(Weekly_Data_Query.Tours)) AS [Avg Of Tours],
Sum(Weekly_Data_Query.Enrollments) AS [Sum Of Enrollments],
Round(Avg(Weekly_Data_Query.Enrollments)) AS [Avg Of Enrollments],
Sum([Weekly_Data_Query]![Tours])/Sum([Weekly_Data_Query]![Inquiries]) AS
ItoT, Sum([Weekly_Data_Query]![Enrollments])/Sum([Weekly_Data_Query]![Tours])
AS TtoE,
Sum([Weekly_Data_Query]![Enrollments])/Sum([Weekly_Data_Query]![Inquiries])
AS ItoE
FROM Weekly_Data_Query
GROUP BY Weekly_Data_Query.School_Num, Weekly_Data_Query.School_Name,
Format$([Weekly_Data_Query].[Week_Ended],'mmmm yyyy'),
Year([Weekly_Data_Query].[Week_Ended])*12+DatePart('m',[Weekly_Data_Query].[Week_Ended])-1
ORDER BY Format$([Weekly_Data_Query].[Week_Ended],'mmmm yyyy') DESC;

I noticed that the report that pulls from this query will run without error
with 2009 dates when I remove the text boxes that average (monthly) the
following fields:

Sum([Weekly_Data_Query]![Tours])/Sum([Weekly_Data_Query]![Inquiries]) AS
ItoT, Sum([Weekly_Data_Query]![Enrollments])/Sum([Weekly_Data_Query]![Tours])
AS TtoE,
Sum([Weekly_Data_Query]![Enrollments])/Sum([Weekly_Data_Query]![Inquiries])
AS ItoE

Again, thanks for your help.
Tyler