query too complex?

[khatley]

I have a table that is about 2000 rows and 155 columns. This is an imported tab delimited file. I have updated a Public Folder hierarchy with new ACLS and have this file that I need to verify I actually got all of the folders. I am trying to querry for an account name called PFassessment, it can be in any cell in the row. I put Not Like "PFassessemnt" in the criteria and it worked fine when I only have 35 fields, but when I do it for 155 it says it is too complex. I thought I could use the * to set criteria but it wont let me do that so any suggestions would be most appreciated.

 

[bat17]

I think that you probably have hit a limit of complexity for Access though I thought it would do 40 columns.

I am not sure why you are using "not like" when you are trying to find the thing though.
I would have thought that you wanted to just search for it in every column with an OR expression.

You may do better by looking for it using VBA where you can just keep looping through all of the records to see if it is there or not.

HTH

Peter

 

[Monkey]

Hi peter there's indeed a limit of complecity in access.
However I have a table which consists of over 200 columns and 70,000 records (don't ask me why).

If you're only looking for not luk "PFassessemnt" just don't select (acitvate) group by. Then you can use the whole table if you like (I'm using Access 2000 and XP)

Greetings monkey

 

[bat17]

The problem lays in how many AND statements that you can have in SQL. Adding 155 separate criteria choked Access.
I think that the limit on Columns is 255 and rows are only limited by memory