"Expression typed incorrectly or too complex." on report subform.

[JayF]

Hi Guys,

I've hit another frustrating braintwister of a stumbling block.

Please find attached a stripped down version of my project containing a report, a subform and the tables and queries they depend on.

On running the report I get the error message:
"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numberic expression may contain too many complicated elements. Try Simplifying the expression by assigning parts of the expression to variables."

The message appears once for each record in tblOrderDetails (tables cropped to prevent hundreds). One of the queries contains calculated fields, I have tried entering these expressions as calculated controls on the sub-form with the same result.

I have checked the formatting of the linked fields.

My sleep deprived brain hurts. Building this invoice is the last hurdle before go it goes live. Any pointers would be appreciated.

Jay

[migi48]

What happens when you run the query? Did you receive the same error? Or it runs successfully?

[JayF]

Hi Migi. All the queries run fine, which is what is puzzling me.

The queries run fine on their own, as does the continuous form, but when you put the two together it squeals. It's almost as if there is a problem with the linked fields, but on one table it is an autonumber field and on the other it is automatically passed across by the entry form subform on which Order Details are entered.

[Mihail]

Jay,
First of all review your tables. You loose some "LookUps" references.

Use the Object Dependencies tool (Ribbon -> Database Tools - Object Dependencies) to discover the problems.

I can't say if this will solve your problem but as long as something is wrong in your database, is a wasted time to look for an answer to your question.

After you make the necessary correction post again the database (if the report still not work).

[JayF]

Thanks Mihail. I looked through the dependencies and found one missing (tblCategories). This was indeed caused by a now redundant LookUp reference which was misnamed. I removed this lookup and retried the report to find that it returns exactly the same error.

All of the objects in this report's dependency tree are included in the sample.

I have reattached the database with the lookup removed.

Thanks again.

[Mihail]

I am almost sure that your problem is caused by use the same query as base for the main report and the sub-form (by the way: use a sub-report instead) because this approach create circular reference.

Create a new query for the details and base the sub_form to this query.
DO NOT refer query eqryInvoice in this new query.

[Mihail]

One more consideration:
So many fields in a single table make me sure that your database is far away from a normalized one.
So take a look here:
http://bytes.com/topic/access/insigh...cle+normalized

[JayF]

Thanks for the info Mihail. I'll have a play with different query configurations and see if I can fix it.


I was under the impression the data was pretty well organised in my DB. I read a similar article to your link before I started developing this DB and have just read the one you linked to. I still don't see how it could be further broken down.

Perhaps with the exception of [Courier] in tblOrders I can't see any other information which could be further split down. [Courier] will only ever contain a single datum (the Courier company's name}, thus splitting it down would surely be overkill?

The address field in tblOrders holds the postage address for that order only, and not necessarily the address in tblContacts. (A button on frmOrders sets the address to the address in tblContacts should it be the same, for usability.)

Using the example I attached, could you please provide me with some hints on how you think it could be improved?

Thanks again.

[NeoPa]

JayF:
Using the example I attached, could you please provide me with some hints on how you think it could be improved?

Not here please. That would be going off the topic of the thread. A separate thread would be required for such a discussion. As it happens, I suspect Mihail saw general, rather than specific, indicators that the DB was not normalised. There may well not be any problems there, but I'll leave you to follow that up, should you be so inclined, in a new thread.

A tip for you here Jay. Typically, we don't like to encourage members to post their projects, without first being requested to do so by someone offering to look at it for them. There are various reasons for this, among which are :

1. It often leads a member into the false assumption that explaining their problem adequately and clearly is not necessary. It is. An attached project is never a substitute for a properly explained situation.
2. It certainly leads to more work for the experts to go through someone's project. Getting involved with a thread should not involve such gotchas for the experts, as we (all of us) don't want to discourage any experts from offering their time. That would be good for none of us.

I explain this to you as I'm sure you'll appreciate what I'm saying. Other comments from you lead me to believe you will understand.

For those occasions where it is necessary, or you've been asked to, the following instructions should minimise the extra effort involved - Before Posting (VBA or SQL) Code. The fact that you've already described it as stripped-down, indicates you probably already understand most of where I'm coming from, but going over it may still help.

[JayF]

Sure Neo. Understood.

The very nonspecific and unhelpful nature of the error I was getting, as well as very little information I could find online, led me to think that the only way of adequately illustrating it was with an example, which I attempted to make as simple as possible by stripping it down.

As it happens, Mihail's advice that it was related to some kind of circular reference (or similar) in the queries, appears to have been right. Thanks for your time, Mihail.

[NeoPa]

I also understand and appreciate that some errors are just so hard to explain Jay. I'm happy that you understand the basic concept and only did it that way in extremis :-)

PS. I'm glad you got your solution, and well done to Mihail for finding it.