CODEWITHSUNDEEP
sqlms-accessms-access-2010
rohrl77
rohrl77

Reputation: 3337

Expression is typed incorrectly Error in a DB that has been working fine for years

I am looking at an MS Access 2010 DB which was designed by someone who left the company years ago. The DB has been in use for years and is split into a Front and Backend.

Now one of the reports in the DB, called via a Macro (not VBA), produces the following error:

This expression is typed incorrectly, or it is too complex to be evaluated

The error implies that there is a mistake in the SQL, so that is where I decided to look first. However, the front and backend are both saved as .ACCDE Files, so it's not possible that any user messed up or changed anything.

When I examined the reports record source, the query executes fine, and I can see results. Here is the SQL:

SELECT [Reporting of Cases].[Case File No],
       [Reporting of Cases].[Date of relevance],
       [Reporting of Cases].Report,
       [Reporting of Cases].[Reported by],
       [Reporting of Cases].[Division Report],
       [Case Database].[Headline (Report)],
       [Case Database].[Case Description (Report)],
       [Case Database].Category,
       [Case Database].Division,
       [Case Database].Organisation,
       [Case Database].Location,
       [Case Database].[CHF at Risk],
       [Case Database].[Risk Flag],
       [Case Database].[Open Date],
       [Reporting of Cases].[Group Report],
       [Reporting of Cases].ID
FROM [Case Database]
  INNER JOIN [Reporting of Cases] 
    ON [Case Database].[Case File No] = [Reporting of Cases].[Case File No]
WHERE ((([Reporting of Cases].[Date of relevance]) BETWEEN Date() AND Date()-45)
  AND (([Reporting of Cases].ReportFlag)=TRUE))
ORDER BY [Reporting of Cases].[Date of relevance] DESC,
         [Reporting of Cases].ID DESC;

The filter used to open the report looks fine as well:

[Date of relevance] Between Date() And Date()-6

It is only when I try to call up the report, regardless of whether I do it manually or via macro, that it produces that error message.

What could cause this sudden error message? This is one of many reports in the DB and the others continue to function as expected.

UPDATE: I have checked the underlying table to ensure that the field Date of relevance is stored as a date. It is and all data entered in the current records is stored as a date as well.

Upvotes: 0

Views: 89

Answers (1)

rohrl77
rohrl77

Reputation: 3337

Taking Andres suggestion, I examined the report further, by deleting controls out of the report. Once the control that was linked to [Case Description (Report)] field in the underlying query was deleted, the report started working again.

Upon examining the records that were linked to that particular control, one that was recorded recently, stuck out from the rest.

The issue was caused by that text field that was in one of the underlying tables. In one of the records in the DB, the field called [Case Description (Report)] contained only

#DELETE

as the entry text. That particular field in the record seemed to remain blocked. I couldn't or delete anything in the field.

Once the entire record was deleted (and afterwards manually reentered without the delete) the DB started to work again!

Upvotes: 2

Related Questions