CODEWITHSUNDEEP
sqlms-access-2007
user2502964
user2502964

Reputation: 1

Access: Expression too complex to be evaluated

I'm trying to sort out values from a database by the weekending date. The script I'm using functions on 6 of my 7 databases (they are all constructed identically). The 7th database doesn't function. I get the expression too complex error. any help figuring out why??

Here is my code:

 SELECT UPC_Test.Type, 
        UPC_Test.[Model No], 
        UPC_Test.[Model Desc], 
        UPC_Test.[Serial No], 
        Format(DateValue([UPC_Test].[Test Date]+7-Weekday([UPC_Test].[Test Date],0)),"m/d/yyyy") AS [Test Date], 
        UPC_Test.Parameter, 
        UPC_Test.[Failure Symptom], 
        UPC_Test.[Repair Action], 
        UPC_Test.[Factory Select], 
        UPC_Test.[Test Station]
FROM UPC_Test
GROUP BY UPC_Test.Type, 
         UPC_Test.[Model No], 
         UPC_Test.[Model Desc], 
         UPC_Test.[Serial No], 
         Format(DateValue([UPC_Test].[Test Date]+7-Weekday([UPC_Test].[Test Date],0)),"m/d/yyyy"), 
         UPC_Test.Parameter, 
         UPC_Test.[Failure Symptom], 
         UPC_Test.[Repair Action], 
         UPC_Test.[Factory Select], 
         UPC_Test.[Test Station]
HAVING (((UPC_Test.Type)="Production") 
AND ((Format(DateValue([UPC_Test].[Test Date]+7-Weekday([UPC_Test].[Test Date],0)),"m/d/yyyy"))=[Enter]) 
AND ((UPC_Test.[Failure Symptom])<>"") 
AND ((UPC_Test.[Repair Action])<>"") 
AND ((UPC_Test.[Test Station])="UPC RF Test"))
ORDER BY Format(DateValue([UPC_Test].[Test Date]+7-Weekday([UPC_Test].[Test Date],0)),"m/d/yyyy");

Upvotes: 0

Views: 2050

Answers (2)

thursdaysgeek
thursdaysgeek

Reputation: 7936

Try reducing your query and fields returned so that you can eliminate parts of it that do not cause any problems. If you can tell what is not causing the error, then you're closer to knowing what is causing the error.

I'd look most closely at the calculated fields and limitations, because perhaps a piece of bad data is resulting in it being undefined (like formatting 30-Feb-2013 to a date).

Try also using a subset of the data in the 7th database, to track down if there is some bad data. Make a copy of the database, make sure the error occurs. Delete the 1st half of the records, see if it still occurs. If it doesn't, get another copy and try it with only the 2nd half of the data. If it fails with 1 half and not the other, it's very likely data, so start deleting half again to track it down.

Upvotes: 0

KeepCalmAndCarryOn
KeepCalmAndCarryOn

Reputation: 9075

A group by clause is used when you have aggregate columns in your select line - such as sum, count. I couldn't see you had any - This is used to restrict the rows returned

So you can get rid of the group by - which collates a query with aggregate columns - and turn the having into a where clause.

Its considered very bad practice to have spaces in your field names and will return to bite you many times - this requires a change to the database schema

I've added the query changes in (untested I have no access)

   SELECT UPC_Test.Type, 
    UPC_Test.[Model No], 
    UPC_Test.[Model Desc], 
    UPC_Test.[Serial No], 
    Format(DateValue([UPC_Test].[Test Date]+7-Weekday([UPC_Test].[Test Date],0)),"m/d/yyyy") AS [Test Date], 
    UPC_Test.Parameter, 
    UPC_Test.[Failure Symptom], 
    UPC_Test.[Repair Action], 
    UPC_Test.[Factory Select], 
    UPC_Test.[Test Station]
 FROM UPC_Test
     WHERE (((UPC_Test.Type)="Production") 
     AND ((Format(DateValue([UPC_Test].[Test Date]+7-Weekday([UPC_Test].[TestDate],0)),"m/d/yyyy"))=[Enter]) 
     AND ((UPC_Test.[Failure Symptom])<>"") 
     AND ((UPC_Test.[Repair Action])<>"") 
     AND ((UPC_Test.[Test Station])="UPC RF Test"))
     ORDER BY Format(DateValue([UPC_Test].[Test Date]+7-Weekday([UPC_Test].[TestDate],0)),"m/d/yyyy");

Upvotes: 1

Related Questions