• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

ACCESS ERROR "..OR IS TOO COMPLEX TO BE EVALUATED..."

I have inherited a couple of databases over here in Warranty and one of them has a minor bug that I can’t seem to find. I have a drop down box on a form, that runs a simple query from a single table, and when we select drop down for August 2011 it will not finish running the query ?  It gives this error message ; again all we do is select the month in the drop down ? I have looked at all data in table see nothing weird or corrupt in the  data and it will pull all other months just fine ??? any advice??  Here is the Qry statement;

SELECT Format$(Resolution_Result_tbl.Date_of_Record,'mmmm yyyy') AS [Month]
FROM Resolution_Result_tbl
GROUP BY Format$(Resolution_Result_tbl.Date_of_Record,'mmmm yyyy'), Format$(Resolution_Result_tbl.Date_of_Record,'mm')
ORDER BY Format$(Resolution_Result_tbl.Date_of_Record,'mm') DESC;
0
castlerj
Asked:
castlerj
  • 3
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
are there any codes running in the Afterupdate event of the combo box?

create a new query and paste this

SELECT Format(Resolution_Result_tbl.Date_of_Record,'mmmm yyyy') AS [Month]
FROM Resolution_Result_tbl
GROUP BY Format$(Resolution_Result_tbl.Date_of_Record,'mmmm yyyy'), Format(Resolution_Result_tbl.Date_of_Record,'mm')
ORDER BY Format(Resolution_Result_tbl.Date_of_Record,'mm') DESC;

see if the query returns result
0
 
Rey Obrero (Capricorn1)Commented:
are there any Null values in field "Date_of_Record" ?
0
 
Rey Obrero (Capricorn1)Commented:
can you create a new form, place a combo box and set the row source to

SELECT Format(Resolution_Result_tbl.Date_of_Record,'mmmm yyyy') AS [Month]
FROM Resolution_Result_tbl
GROUP BY Format$(Resolution_Result_tbl.Date_of_Record,'mmmm yyyy'), Format(Resolution_Result_tbl.Date_of_Record,'mm')
ORDER BY Format(Resolution_Result_tbl.Date_of_Record,'mm') DESC;


post how it goes..
0
 
Nick67Commented:
Going sideways as it were...
Why have the query doing all this conversion, grouping and ordering?
Is it just returning a list of possible months and years?

The query could then be

SELECT DISTINCT Format(Resolution_Result_tbl.Date_of_Record,'mmmm yyyy') AS [Month]
FROM Resolution_Result_tbl WHERE Format(Resolution_Result_tbl.Date_of_Record,'mmmm yyyy')  is not null
ORDER BY Format(Resolution_Result_tbl.Date_of_Record,'mmmm yyyy') DESC;

That'll get you months and years in decending order, and handle any nulls
0
 
castlerjSenior AnalystAuthor Commented:
I adjsuted the query to capicorn1's suggestion and all is working fine.  I will also try Nick67 too.  Thanks for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now