• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • 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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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