CODEWITHSUNDEEP
sqlms-accessms-access-2013
Macellaria
Macellaria

Reputation: 71

Out of nowhere getting the "The expression is typed incorrectly or too complex" error on a union query that I frequently use on Access

I have a union query that pulls together data from a couple other queries. These queries search for data based on the date that is entered on a form.

I have been using this query for the longest time, and even have backups of this from months ago, when I know it worked, but suddenly, this error pops up instead.

The Union Query

SELECT MnthlyFireExCnt.ProjectCriteria AS Project, MnthlyFireExCnt.CountOfFire_Extinguisher_Location_ID_ AS NumberOf, MnthlyFireExCnt.Type
FROM MnthlyFireExCnt

UNION ALL 

SELECT MnthlyFrstAdCnt.ProjectCriteria, MnthlyFrstAdCnt.CountOfForm_Record_Number, MnthlyFrstAdCnt.Type
FROM MnthlyFrstAdCnt

UNION ALL 

SELECT MnthlyIncCnt.ProjectCriteria, MnthlyIncCnt.CountOfDate_Time_Reported, MnthlyIncCnt.Occurrence
FROM MnthlyIncCnt

UNION ALL 

SELECT MnthlyJHACnt.ProjectCriteria, MnthlyJHACnt.CountOfDate, MnthlyJHACnt.Type
FROM MnthlyJHACnt

UNION ALL 

SELECT MnthlySiteObsvCnt.ProjectCriteria, MnthlySiteObsvCnt.CountOfTime, MnthlySiteObsvCnt.Type
FROM MnthlySiteObsvCnt

UNION ALL 

SELECT MnthlySSICnt.ProjectCriteria, MnthlySSICnt.CountOfDate, MnthlySSICnt.InspCriteria
FROM MnthlySSICnt

UNION ALL 

SELECT MnthlyToolTCnt.ProjectCriteria, MnthlyToolTCnt.CountOfDate_Time, MnthlyToolTCnt.Type
FROM MnthlyToolTCnt


UNION ALL SELECT MnthlyWSSCnt.ProjectCriteria, MnthlyWSSCnt.CountOfForm_Record_Number, MnthlyWSSCnt.Type
FROM MnthlyWSSCnt;

And this is one of the query that pulls the data together for the Union Quer.

SELECT SSIReportsProjectCrit.ProjectCriteria, Count(MnthlyFireExData.Fire_Extinguisher_Location_ID_) AS CountOfFire_Extinguisher_Location_ID_, "Fire Extinguisher(s) Inspected" AS Type
FROM SSIReportsProjectCrit LEFT JOIN MnthlyFireExData ON SSIReportsProjectCrit.ProjectCriteria = MnthlyFireExData.Site_Location
GROUP BY SSIReportsProjectCrit.ProjectCriteria, "Fire Extinguisher(s) Inspected";

This query works no problem. It is pulling up the data.

Any help would be extremely appreciated!

Upvotes: 1

Views: 76

Answers (1)

sbgib
sbgib

Reputation: 5838

Test that the unioned queries work individually.

  • If they don't, something in the design of the tables may have changed. In any case, check that the queries are returning data of the same type.
  • If they do, and you know that a) the whole union query has not changed at all and b) that it has worked in the past, then your intuition that the query is and has been fine is likely correct.

In my experience, I have found that this can also be due to corruption of the Access files. Sometimes, I would check for this first if I couldn't find any other logical explanation. In such cases, what has worked for me has been:

  1. Attempt a Compact & Repair of the relevant file(s)
  2. If the problem persists, then create a new Access file and import all objects from the old file to the new one, add any VBA references/libraries, compile it, Compact & Repair and then see if that fixes it.

Upvotes: 0

Related Questions