Error: Expression too complex to be calculated

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
I have a simple process that involves a form, query, and report. The form has list boxes pulling info from separate queries quering a data table. This form is then referenced in my main query's criteria, which is to be populated into my main report. I have a command button on my form which pulls my report in preview mode once the criteria's are passed to my main query.

I have successfully set up this process for other databases, but for some reason I get an error stating: ... too complex to be calculated.... however, the only difference in this set up than the other successful ones I have created is that the main data table has a huge amount of data spreading 50,000 rows..

I am really stuck on this one .... Any advice or suggestion would be greatly appreciated...

Thanks in advance,
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
Here is my main queries SQL statement, this may help find the problem:

Code:
SELECT [PREMIUM TABLE].SITE, [PREMIUM TABLE].GROUP, [PREMIUM TABLE].DATE_, [PREMIUM TABLE].[CAMPAIGN NAME], [PREMIUM TABLE].PLACEMENT, [PREMIUM TABLE].[IMPRESSIONS DELIVERED], [PREMIUM TABLE].[TOTAL BOOKED IMPRESSIONS], [PREMIUM TABLE].[TOTAL GROSS REVENUE], [PREMIUM TABLE].[TOTAL NET REVENUE]
FROM DATA_TABLE, [PREMIUM TABLE]
WHERE ((([PREMIUM TABLE].SITE) Like [Forms]![Premium Campaign Form]![SiteComboBox1] & "*") AND (([PREMIUM TABLE].GROUP) Like [Forms]![Premium Campaign Form]![GroupComboBox1] & "*") AND (([PREMIUM TABLE].DATE_) Between [Forms]![Premium Campaign Form]![StartDateComboBox1] And [Forms]![Premium Campaign Form]![EndDateComboBox2] & "*"));
 

huh?

Board Regular
Joined
Aug 3, 2004
Messages
72
General limits on access, see <a href="http://www.databasedev.co.uk/access_specifications.html">here</a>.

For limits on expression complexity, see <a href="http://www.utteraccess.com/forums/showflat.php?Cat=&Number=1487750">here</a>.
The limit on nested iif statements is either 7 or 9.

For a work-around solution you can also try to shorten your sql statement by using aliases. You can also use the suggestion <a href="http://en.allexperts.com/q/Using-MS-Access-1440/access-2000-query-design-1.htm">here</a>.

Hope this helps.
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
I have only used Access sparingly, but I came accross this issue once and it was when I was doing division and one of my denominators had a 0 and that was the error I received when Access tried to divide by 0. If there is any division being done I'd check the denominators.

Hope that helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top