Limits of MS ACCESS?

[Vulcan1500]

In my application I build a strSQL of which the strWhere part is built via a form with selection buttons. After execution a search using the strSQL a number of records are filtered and displayed in a listbox. Then via a preview button a report is made and shown.

So far everything runs as expected and this is also the case when I increase the selection by clicking more selection buttons.

Suddenly, after increasing the selection by clicking an extra button, it fails. The report displays all the data, while the listbox is still showing the data based on the strSQL.

I use the following command to print the data:

DoCmd.OpenReport "rptExecutedProject", acPreview, strSQL

This is the content of the strSQL when the problem occurs. In red you can see the extra selection. Before that things ran as expected.

([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkPrimaryActivity] = 11) OR ([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkSecondaryActivity] = 11) OR ([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkTertiaryActivity] = 11) OR ([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkPrimaryActivity] = 12) OR ([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkSecondaryActivity] = 12) OR ([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkTertiaryActivity] = 12) OR ([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkPrimaryActivity] = 13) OR ([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkSecondaryActivity] = 13) OR ([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkTertiaryActivity] = 13) OR ([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkPrimaryActivity] = 14) OR ([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkSecondaryActivity] = 14) OR ([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkTertiaryActivity] = 14) [COLOR=red]OR ([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkPrimaryActivity] = 15) OR ([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkSecondaryActivity] = 15) OR ([DateEnd] BETWEEN #11/20/1998# AND #11/20/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkTertiaryActivity] = 15)[/COLOR]

Pls help me with this nasty problem. First I thought I was over the limits of MS Access but I'm unable to find the reason and on top of that any consistancy. Thanks in advance!

 

[DCrake]

If you copy this where condition into a query do it work?

Also I think there must be a better way to code the condition. If you can send a sample table and query I can look at the logic for you.

At present there are 2321 characters in the string (No I did not count them, I pasted them into a word document and went in to tools word count)

You may try looking at In(1,3,4)

Also remove the date condition form the query. Save the query. Create a new query, drag all the fields down and apply your date rannge in the new query. This way the syntax only appears once in the sql.

David

 

[Vulcan1500]

I'm working on your advices David. Thanks for that! What do you mean with "You may try looking at In(1,3,4)"?

 

[missinglinq]

Here are the Access limits for Queries:

Number of enforced relationships - 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships
Number of tables in a query - 32
Number of fields in a recordset - 255
Recordset size -1 gigabyte
Sort limit - 255 characters in one or more fields
Number of levels of nested queries - 50
Number of characters in a cell in the query design grid - 1,024
Number of characters for a parameter in a parameter query - 255
Number of ANDs in a WHERE or HAVING clause - 99
Number of characters in an SQL statement approximately - 64,000

For the full list of Access specs:

http://bytes.com/forum/thread603897.html

 

[MSAccessRookie]

I'm working on your advices David. Thanks for that! What do you mean with "You may try looking at In(1,3,4)"?

I think he means using In() instead of all of the Ors in each group

([fkType] = 1 [COLOR=red][B]Or[/B][/COLOR] [fkType] = 2 [B][COLOR=#ff0000]Or[/COLOR][/B] [fkType] = 3)
 
Becomes 
 
([fkType] [COLOR=red][B]In[/B][/COLOR] (1, 2, 3))

 

[Vulcan1500]

Thanks for all your advices! I'm getting closer to the problem causing the faulty report, but still not able to solve. I've been adding criteria one by one and every time I checked the content of the strSQL, listbox and report. Underneath you will find the strSQL when the first faulty report was produced (all records of db instead of records displayed in listbox) and the last criteria I added was 'activety 25'.

SELECT qrySearch.pkProjectID, qrySearch.Country, qrySearch.Location, qrySearch.DateStart, qrySearch.DateEnd, qrySearch.ProjectDescription, qrySearch.UltimateClient, qrySearch.TotalContractValue, qrySearch.JVSharePercentageVO, qrySearch.fkType, qrySearch.TypeDescription, qrySearch.AwardSheet, qrySearch.ProjectNo, qrySearch.ProjectName, qrySearch.fkArea, qrySearch.Area, qrySearch.fkSubArea, qrySearch.SubArea, qrySearch.Consultant, qrySearch.MaintenancePeriod, qrySearch.ContractDescription, qrySearch.MainContractor, qrySearch.JointVenture, qrySearch.JVPartner, qrySearch.JVShareValueVO, qrySearch.DateAward, qrySearch.Duration, qrySearch.fkPrimaryActivity, qrySearch.PrimaryActivity, qrySearch.QuantityPrimaryActivity, qrySearch.PrimaryUnit, qrySearch.fkSecondaryActivity, qrySearch.SecondaryActivity, qrySearch.QuantitySecondaryActivity, qrySearch.SecondaryUnit, qrySearch.fkTertiaryActivity, qrySearch.TertiaryActivity, qrySearch.QuantityTertiaryActivity, qrySearch.TertiaryUnit, qrySearch.fkBD, qrySearch.BDName, qrySearch.fkBHD, qrySearch.BHDName, qrySearch.fkCSD, qrySearch.CSDClass, qrySearch.CSDName, qrySearch.fkFFPV, qrySearch.FFPVName, qrySearch.fkSSDV, qrySearch.SSDVName, qrySearch.fkTSHD, qrySearch.TSHDClass, qrySearch.TSHDName, qrySearch.fkWID, qrySearch.WIDName FROM qrySearch WHERE 
([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkPrimaryActivity] = 12) OR ([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkSecondaryActivity] = 12) OR ([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkTertiaryActivity] = 12) OR ([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkPrimaryActivity] = 14) OR ([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkSecondaryActivity] = 14) OR ([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkTertiaryActivity] = 14) OR ([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkPrimaryActivity] = 16) OR ([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkSecondaryActivity] = 16) OR ([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkTertiaryActivity] = 16) OR ([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkPrimaryActivity] = 24) OR ([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkSecondaryActivity] = 24) OR ([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkTertiaryActivity] = 24) OR ([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkPrimaryActivity] = 25) OR ([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkSecondaryActivity] = 25) OR ([DateEnd] BETWEEN #11/21/1998# AND #11/21/2008#) AND ([fkType] = 1 OR [fkType] = 2 OR [fkType] = 3) AND ([fkArea] = 7) AND ([fkTertiaryActivity] = 25) 
ORDER BY qrySearch.Country ASC, qrySearch.Location ASC, qrySearch.DateStart DESC

I'm convinced that this strSQL is also sent to command:

DoCmd.OpenReport "rptExecutedProject", acPreview, strSQL

I'm not certain if this command can take the length of the string.

When I add the strSQL in the query the result is equal to the listbox and that is good. Viewing the query in design mode the criteria are as I expected in my code. I accept the code can be more efficient, but for the moment it does what I want it to do. Only a suddenly faulty report makes me !!!

 

[LPurvis]

I agree that this issue doesn't have anything to do with the limits of Access (or Jet for that matter).

Your final post includes the SELECT and FROM clauses of the SQL.
You'd post just the criteria of a WHERE clause (without the WHERE keyword) to filter a Report in it's open method - however, based on what you've posted, it seems to me that since the start of this question you've been passing it in the wrong parameter.
i.e.
DoCmd.OpenReport "rptExecutedProject", acPreview, strSQLpasses strSQL to the FilterName parameter.
You'd want
DoCmd.OpenReport "rptExecutedProject", acPreview, , strSQL

It's just one reason why I often prefer specifying parameters by name (you don't remember / guess at parameter position).

 

[Vulcan1500]

You are right Leigh. My fault, but if I use

DoCmd.OpenReport "rptExecutedProject", acPreview, ,strSQL

still I get all the data in the report and if I use (the strWhere is also known)

DoCmd.OpenReport "rptExecutedProject", acPreview, strWhere

then MS Access complains that the string is too long

 

[LPurvis]

I'm afraid they're meaningless to me unless the exact contents of strSQL and strWhere are given.

 

[Vulcan1500]

strSQL which includes the strWhere is in my previous post (today 12:14) Leigh. Is that what you need?

 

[LPurvis]

Have you not edited that though - given when I'd mentioned?

I take it strWhere contains the where clause but without the WHERE keyword - so everything from the second line onwards - beginning with:
([DateEnd] BETWEEN...

And hence
DoCmd.OpenReport "rptExecutedProject", acPreview, , strWhere
doesn't work?

That said - it is over 2000 characters. That's a substantial filter request.
(I've known including a trailing semi colon to remove Access initial distaste of long SQL statements in UI objects - even though it's not generally required to terminate a SQL statement).

However I'd probably look to alter the defintion of the querydef upon which the report is based before opening the report.
e.g.
CurrentDb.QueryDefs("YourQueryName").SQL = strSQL
DoCmd.OpenReport "rptExecutedProject", acPreview

 

[Vulcan1500]

It took me some time to come to a solution of this problem with all your advices. Thanks for that. After the query is built I store it and use it once the report is made. This seems to work perfect!