Error 3071 - too complex to be evaluated

[jojones]

I am constantly getting this error when running a query.

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

There are several custom functions (returning strings, doubles, integers) in the query. There are functions that use the results of other functions within the query to return a result.

I believe all my function code is right and that all my syntax within the query is also right. Can anyone tell me more about this problem? Most likely causes.. fixes..
thanks
Jo

 

[smedvid]

Well, I've hit this one before myself. I had a query that was 3 feet long in Where clauses. My solution was to break down the big query into 3 or 4 smaller less complex queries then combine the results. And, in my specific case (million record tables) there was a significant performance gain in breaking down a huge query into smaller more digestable pieces.

I also tried to isolate where the problem was and develop a work-around within the huge query, but never got it.

htwh.... Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/

 

[jojones]

Thanks Steve

Incase my experience will help anyone...

After a lot of elimination/investigation we actually found out that it was one field creating the problem. It was a field (DateToday) which returns Date() ie today's date. For some reason it doesn't work anymore, although it worked fine last month.

Since last month we have moved the db onto a new server which seems to have had some side effects on the queries. I changed DateToday to Now() and it worked, however I did not want Now(), so I created a custom function to return a date in the appropriate format and that works fine too! Very bizarre!

Anyway, it is also having an issue with Mid([FIELD],2,4) which is returning a #Error. There is no problem with the data at all. Using a custom function solves this problem also.

Any ideas why this might be happening on the new server?
Thanks
Jo

 

[jobo123]

Hi JoJones,

A couple of potentially useful offerings:
1. I have used some very large select queries in Access97. The largest was 13,198 characters (when counted in word), so the size of you query will not be an issue if it is lower than this.
2. I have seen queries fail because there were references missing in the database. Check this by opening a module in design view (create a new one if you have none), and then from the toolbar, choose View, References. If anything is 'MISSING' this can cause a problem.
3. Thirdly if it really is you sql which is breaking language rules, you need to break the problem down (as suggested by Smedvid). One way without splitting up the query is to (NB back up first) progressively delete calculated fields from the query, and keep going till it does run. This should help identify the bad expression.
Hope this is of some help, jobo123