Error message out of the blue

lbs

Registered User.
Local time
Today, 13:47
Joined
Dec 22, 2019
Messages
109
Hello!
I have been using my database for quite a long time. BTW I constructed it with a great help of experts on this forum.
So, today, out of the blue, each time I move to the next record on one of the forms, I started receiving a message: Expression too complex in query expression '((((((((((((((((( [q_r_14_activities_n].[Cons_act]="RR))))))))))) AND ........ and so on quite long message".

I am puzled. I did not make any changes to any structure of the database. And this is very annoying as I did not make a backup copy recently.

I wonder if I again, like year ago cam find help on this forum

Many thank
lbs
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:47
Joined
Oct 29, 2018
Messages
21,357
Maybe the error has something to do with "bad data." Double-check your data to see if there's anything wrong with any of them.
 
  • Like
Reactions: lbs

lbs

Registered User.
Local time
Today, 13:47
Joined
Dec 22, 2019
Messages
109
Maybe the error has something to do with "bad data." Double-check your data to see if there's anything wrong with any of them.
I don't think it's wrong data. The database is fully normalised and everything i choose from dropdown boxes
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:47
Joined
Feb 28, 2001
Messages
26,999
Go to the Windows Update page (which you can find via Windows Start >> Settings) and look to the update history. It should tell you if you recently had an update of Office (in general) or Access (in specific). For things that used to work and now have stopped, that is a worthwhile check.

We can't see the whole statement, but I worry about the part that I see. From a technical viewpoint, every left parenthesis increases the complexity of the expression by one level on the internal expression stack because it is a signal to the compiler that a complex expression potentially follows. So it has to push something on the internal expression stack as a placeholder. Since we can't see the whole expression it is hard to imagine how that double-quote followed by so many right parentheses will parse correctly, but as to what I CAN see, you have a badly unbalanced grouping of parentheses.

In this article, there is some hint that VBA expressions have a limit of 8 levels of nesting.


There is plenty of evidence that SQL expressions also have limits though I can't find exact specifications for them. In your expression, you have a lot more than 8 left parentheses sequentially in a string. The usual approach for fixing the "too complex" problem is to break the expression into parts or put it in a function that can apply multiple statements to break up the expression and reduce the complexity of the parts by splitting them.
 
  • Like
Reactions: lbs

lbs

Registered User.
Local time
Today, 13:47
Joined
Dec 22, 2019
Messages
109
Maybe the error has something to do with "bad data." Double-check your data to see if there's anything wrong with any of them.
What is interesting, when I switch to design view and then back to form view this message is gone. But after I close the form and open it, it's back again.
 

isladogs

MVP / VIP
Local time
Today, 13:47
Joined
Jan 14, 2017
Messages
18,186
In order that anyone may be able to help you, please provide
1. The full error message you are getting. Is there an error number?
2. The SQL of the query or form record source

As DBG suggested, you may have corrupted data. Alternatively, either your app or Access itself may be corrupted.
Does the project compile? Have you tried running a compact and repair? Or a decompile? (run a backup first)
Or importing all objects into a new blank database? Or running an Office repair?
 

lbs

Registered User.
Local time
Today, 13:47
Joined
Dec 22, 2019
Messages
109
Go to the Windows Update page (which you can find via Windows Start >> Settings) and look to the update history. It should tell you if you recently had an update of Office (in general) or Access (in specific). For things that used to work and now have stopped, that is a worthwhile check.

We can't see the whole statement, but I worry about the part that I see. From a technical viewpoint, every left parenthesis increases the complexity of the expression by one level on the internal expression stack because it is a signal to the compiler that a complex expression potentially follows. So it has to push something on the internal expression stack as a placeholder. Since we can't see the whole expression it is hard to imagine how that double-quote followed by so many right parentheses will parse correctly, but as to what I CAN see, you have a badly unbalanced grouping of parentheses.

In this article, there is some hint that VBA expressions have a limit of 8 levels of nesting.


There is plenty of evidence that SQL expressions also have limits though I can't find exact specifications for them. In your expression, you have a lot more than 8 left parentheses sequentially in a string. The usual approach for fixing the "too complex" problem is to break the expression into parts or put it in a function that can apply multiple statements to break up the expression and reduce the complexity of the parts by splitting them.
It started at work and continues with the same file o my home computer. The last update was 3 days ago and it stopped working today. Yesterday was fine
 

lbs

Registered User.
Local time
Today, 13:47
Joined
Dec 22, 2019
Messages
109
In order that anyone may be able to help you, please provide
1. The full error message you are getting. Is there an error number?
2. The SQL of the query or form record source

As DBG suggested, you may have corrupted data. Alternatively, either your app or Access itself may be corrupted.
Does the project compile? Have you tried running a compact and repair? Or a decompile? (run a backup first)
Or importing all objects into a new blank database? Or running an Office repair?
The project compiles. I did compact and repair to no avail. How to decompile? I will try now to import everything to a blank database
 

lbs

Registered User.
Local time
Today, 13:47
Joined
Dec 22, 2019
Messages
109
In order that anyone may be able to help you, please provide
1. The full error message you are getting. Is there an error number?
2. The SQL of the query or form record source

As DBG suggested, you may have corrupted data. Alternatively, either your app or Access itself may be corrupted.
Does the project compile? Have you tried running a compact and repair? Or a decompile? (run a backup first)
Or importing all objects into a new blank database? Or running an Office repair?
Imported into a blank database. same problem
 

lbs

Registered User.
Local time
Today, 13:47
Joined
Dec 22, 2019
Messages
109
In order that anyone may be able to help you, please provide
1. The full error message you are getting. Is there an error number?
2. The SQL of the query or form record source

As DBG suggested, you may have corrupted data. Alternatively, either your app or Access itself may be corrupted.
Does the project compile? Have you tried running a compact and repair? Or a decompile? (run a backup first)
Or importing all objects into a new blank database? Or running an Office repair?
The full message is this
1608676166182.png


The SQL is probably this:

SELECT q_s_00_rotas_dates.Year, q_s_00_rotas_dates.rota_id, q_s_00_rotas_dates.Rota_duration, q_s_00_rotas_dates.Week_no, q_s_00_rotas_dates.week_day_name, q_s_00_rotas_dates.week_day_date, t_r_14_activities_n.date_act, t_r_14_activities_n.Cons_act, t_r_14_activities_n.activity, t_s_07_activities.Act_s, " " & [Act_s] & " - " & [Cons_act] AS Act_Cons_str, ConcatRelated("[Act_Cons_str]","[q_r_14_activities_n]","Date_act = #" & Format([Date_act],"mm/dd/yyyy") & "#") AS cons_act_date_concat_str
FROM t_s_07_activities INNER JOIN (q_s_00_rotas_dates INNER JOIN t_r_14_activities_n ON q_s_00_rotas_dates.Date = t_r_14_activities_n.date_act) ON t_s_07_activities.Act_id = t_r_14_activities_n.activity
ORDER BY t_r_14_activities_n.date_act, t_r_14_activities_n.activity;
 

isladogs

MVP / VIP
Local time
Today, 13:47
Joined
Jan 14, 2017
Messages
18,186
I agree with Doc. There are far too many brackets (parentheses).
The SQL is probably this....
I would say the SQL is definitely not that as it bears no resemblance to the error message.
 

Isaac

Lifelong Learner
Local time
Today, 06:47
Joined
Mar 14, 2017
Messages
8,738
I'd say you should put a breakpoint in the function ConcatRelated() and see what the heck is going on there to produce a million parenthesis.
 
  • Like
Reactions: lbs

lbs

Registered User.
Local time
Today, 13:47
Joined
Dec 22, 2019
Messages
109
I'd say you should put a breakpoint in the function ConcatRelated() and see what the heck is going on there to produce a million parenthesis.
How do I put a breakpoint? I am not very sophisticated In this. I constructed this db year ago and since then Becaus€ everything worked fine and didn’t require tuning I forgot most of the plot.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:47
Joined
Feb 28, 2001
Messages
26,999
The SQL you quoted and the message you captured do not match. Your SQL does not contain that string of parentheses and does not contain a quoted string of "RR" anywhere.

Now that I see the actual message, I see what amounts to a simple relational expression buried in a ton of parentheses. If you can find the query that contains that string of left parentheses, you can eliminate them from the inside out in Left/Right pairs. If you are BUILDING that query dynamically, you need to revisit the code. But the first trick in making rabbit stew is to catch the rabbit. You MUST find the query that contains that sequence.

To set breakpoints you must have the VBA code window open. In that window, you will have some code. If you have an executable statement of any kind, you should be able to left-click in the left margin of the code. One of two things will happen. Either you will highlight some of the code if you are too far to the right, or you will create a dark red dot in the left margin opposite the line. If you succeed in creating the breakpoint, you can then run the code by running your app in the normal way. Execution will stop BEFORE you execute a line with a breakpoint. (You can set more than one breakpoint at a time.) When you are done debugging, there is a menu item under DEBUG to "clear all breakpoints." Beware of leaving breakpoints set when distributing a new front end file.
 

Isaac

Lifelong Learner
Local time
Today, 06:47
Joined
Mar 14, 2017
Messages
8,738
How do I put a breakpoint? I am not very sophisticated In this. I constructed this db year ago and since then Becaus€ everything worked fine and didn’t require tuning I forgot most of the plot.
go to the vba project. (Alt + F11). double click any module (left hand) to go into the code so your cursor is blinking somewhere inside code. Ctrl+F to find ConcatRelated(). On the first line of its code, click the left hand side (to the left of the code text) to set the breakpoint. You may have to google a video if uncertain.

Edit oops i see TDM already mentioned you about the breakpoint.
Sorry my time on AWF has been next to nothing recently and I have my regular routine all thrown out of place.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:47
Joined
Feb 19, 2013
Messages
16,553
I suspect the concatrelated function has been modified in some way, or the data it is processing has errors or the criteria applied is incorrect. The q_r_14_activities_n query is passed as a parameter so perhaps the code is in a loop adding () to the criteria
 

lbs

Registered User.
Local time
Today, 13:47
Joined
Dec 22, 2019
Messages
109
The SQL you quoted and the message you captured do not match. Your SQL does not contain that string of parentheses and does not contain a quoted string of "RR" anywhere.

Now that I see the actual message, I see what amounts to a simple relational expression buried in a ton of parentheses. If you can find the query that contains that string of left parentheses, you can eliminate them from the inside out in Left/Right pairs. If you are BUILDING that query dynamically, you need to revisit the code. But the first trick in making rabbit stew is to catch the rabbit. You MUST find the query that contains that sequence.

To set breakpoints you must have the VBA code window open. In that window, you will have some code. If you have an executable statement of any kind, you should be able to left-click in the left margin of the code. One of two things will happen. Either you will highlight some of the code if you are too far to the right, or you will create a dark red dot in the left margin opposite the line. If you succeed in creating the breakpoint, you can then run the code by running your app in the normal way. Execution will stop BEFORE you execute a line with a breakpoint. (You can set more than one breakpoint at a time.) When you are done debugging, there is a menu item under DEBUG to "clear all breakpoints." Beware of leaving breakpoints set when distributing a new front end file.
Thank you very much The_Doc_Man. I’ll keep trying to do something although it seems very complicated. I am just puzzled why it happened. It worked fine for many months. I didn’t make any changes, only added data. And all of a sudden the glitch started. I am frustrated about how unstable ms access is.
I suspect the concatrelated function has been modified in some way, or the data it is processing has errors or the criteria applied is incorrect. The q_r_14_activities_n query is passed as a parameter so perhaps the code is in a loop adding () to the criteria
I did not modify concatrelated function. And actually this function has nothing to do with this particular form. It is used to create a report. This form uses another query. But, yes one of the subforms uses the query with concatrelated function, but I deleted that subform and the problem still persists.
I tried to delete all data from the last month in case if I entered anything incorrect. Nothing helps.
It's weird that when I go to design view and then back to form view, the form works perfect until I close it and open again.
 

lbs

Registered User.
Local time
Today, 13:47
Joined
Dec 22, 2019
Messages
109
I solved the problem for now.
I realised (too slowly of course) that on my google drive there should be many previous versions in trash. So I took one two weeks ago version and manually added all data for those two weeks table to table. Now everything works.

But still to me it's a mystery what happened. I did not make any changes to the structure or codes or anything. I was adding data only. Now I added the same data in full to the earlier version of DB and it works perfectly. I am still suspicious for MS Access. Its very unstable.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:47
Joined
Oct 29, 2018
Messages
21,357
I solved the problem for now.
I realised (too slowly of course) that on my google drive there should be many previous versions in trash. So I took one two weeks ago version and manually added all data for those two weeks table to table. Now everything works.

But still to me it's a mystery what happened. I did not make any changes to the structure or codes or anything. I was adding data only. Now I added the same data in full to the earlier version of DB and it works perfectly. I am still suspicious for MS Access. Its very unstable.
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

onur_can

Active member
Local time
Today, 06:47
Joined
Oct 4, 2015
Messages
180
In which access version does your application run regularly, in which version do you get an error message? It probably works in the old version, if not in the new version, it may be due to differences in data types. It is necessary to examine and analyze DB.
 

Users who are viewing this thread

Top Bottom