query is too complex error message

A

Andrea

does anyone know if access 2007 has increased the capabilities of their
queries? i have a query that was built in the 2003 version that keeps giving
me the subject line error message. the query includes 7 fields that have a
criteria requirements. it works for about a month and then just stops
working. any help would be appreciated.

thanks,
andrea
 
J

John Vinson

Andrea said:
does anyone know if access 2007 has increased the capabilities of their
queries? i have a query that was built in the 2003 version that keeps giving
me the subject line error message. the query includes 7 fields that have a
criteria requirements. it works for about a month and then just stops
working. any help would be appreciated.

thanks,
andrea
--
Click to expand...


I don't think it's an Access version problem; it's much more likely a
problem with the Query. Seven criteria is nothing much, and the query "going
bad" on its own strongly suggests database corruption. Have you tried
Tools... Database utilities... Compact and Repair?

Perhaps you could post the SQL of the query to see if anyone can see a
specific problem.
 
A

Andrea

the query criteria is as follows:

([forms]![frmSearchByMultipleFunctions]![ThisValue5]
or([forms]![frmSearchByMultipleFunctions]![ThisValue5] is Null))

This string is found in 7 fields, matching ThisValue1 through ThisValue7.

I tried Compact and Repair and it did not fix the issue.
 
J

John W. Vinson

the query criteria is as follows:

([forms]![frmSearchByMultipleFunctions]![ThisValue5]
or([forms]![frmSearchByMultipleFunctions]![ThisValue5] is Null))

This string is found in 7 fields, matching ThisValue1 through ThisValue7.
Click to expand...

If you have seven repeating fields in your table design, it REALLY sounds like
the root of the problem is not the query - but that your table is not
correctly normalized! "Fields are expensive, records are cheap" - if you have
a one (record) to many (value) relationship, it should be in two tables in a
one to many relationship.

If these are in fact non-repeating, non-interdependent fields, then you may
want to consider a different approach. If you open this query in SQL view and
toggle back and forth to the design grid, you'll see what a horrid hash
Access makes of it. For seven (independent, normalized!!!!) fields, it may be
worth a bit of extra effort to write VBA code to construct the SQL string in
code, by looping through the form, and adding each non-NULL control's value to
a SQL query string. You can then use that string as the Recordsource for a
form or report.

Post back if you need help setting this up...

John W. Vinson [MVP]
 
A

Andrea

John- I cant thank you enough for your help. However, I am fairly new at
this and pretty much self-taught, so I am not familiar with some of the
vocabularly. Can you explan what you mean by normalized?

Thanks.
--
Andrea


John W. Vinson said:
the query criteria is as follows:

([forms]![frmSearchByMultipleFunctions]![ThisValue5]
or([forms]![frmSearchByMultipleFunctions]![ThisValue5] is Null))

This string is found in 7 fields, matching ThisValue1 through ThisValue7.
Click to expand...

If you have seven repeating fields in your table design, it REALLY sounds like
the root of the problem is not the query - but that your table is not
correctly normalized! "Fields are expensive, records are cheap" - if you have
a one (record) to many (value) relationship, it should be in two tables in a
one to many relationship.

If these are in fact non-repeating, non-interdependent fields, then you may
want to consider a different approach. If you open this query in SQL view and
toggle back and forth to the design grid, you'll see what a horrid hash
Access makes of it. For seven (independent, normalized!!!!) fields, it may be
worth a bit of extra effort to write VBA code to construct the SQL string in
code, by looping through the form, and adding each non-NULL control's value to
a SQL query string. You can then use that string as the Recordsource for a
form or report.

Post back if you need help setting this up...

John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

John- I cant thank you enough for your help. However, I am fairly new at
this and pretty much self-taught, so I am not familiar with some of the
vocabularly. Can you explan what you mean by normalized?
Click to expand...

Others have done so much better than I could off the cuff. Read the "Database
Design 101" links on Jeff's page:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]
 
Top