Query is too complex

[Guest]

After running the following query a few times
I started getting an error "Query is too complex" .
Would you happen to know why this would work a few times, then get this error?
Thanks

SELECT [Table1].Region, [Table2].[IDNumber], [Table2].[InstallDate],
[Table2].[StartDate], [Table2].[StartDate], [Table2].[ EndDate],
[Table2].Place, [Table2].PM, [Table2].[Tech] FROM [Table2] INNER JOIN
[IDNumber] ON [Table2].[IDNumber] = [Table1].[IDNumber]
WHERE (([Table1].Region = [Forms]![SearchForm]![Region] OR
[Forms]![SearchForm]![Region] IS NULL)
AND ([Table2].[IDNumber] =[Forms]![SearchForm]![ IDNumber] OR
[Forms]![SearchForm]![ IDNumber] IS NULL)
AND ([Table2].[InstallDate]= [Forms]![SearchForm]![ InstallDate] OR
[Forms]![SearchForm]![ InstallDate] IS NULL)
AND ([Table2].[StartDate] = [Forms]![SearchForm]![StartDate] OR
[Forms]![SearchForm]![StartDate] IS NULL)
AND ([Table2].[EndDate] = [Forms]![SearchForm]![EndDate] OR
[Forms]![SearchForm]![EndDate] IS NULL)
AND ([Table2].[Place] = [Forms]![SearchForm]![ Place] OR
[Forms]![SearchForm]![ Place] IS NULL)
AND ([Table2].PM = [Forms]![SearchForm]![ PM] OR [Forms]![SearchForm]![ PM]
IS NULL)
AND ([Table2].Tech = [Forms]![SearchForm]![ Tech] & "*" OR
[Forms]![SearchForm]![ Tech] IS NULL));

 

[John Spencer]

What you posted seems to be invalid SQL to me. As you seem to have a reference
to a field where you should have a reference to a table, there are spaces as the
first character of field names and control names.

What you posted must have been typed in since there are also extra spaces in
field names and control names. etc.

I will say that Access will mangle this query into unreadability and complexity
with this many conditions in the WHERE clause using the structure of
[SomeField] = SomeControl or SomeControl is Null
Each time you add one of these type clauses to the query, Access creates one
line for each combination of factors. So the number of conditions in the where
clause increases rapidly.

You probably need to write the where clause for this query using some vba to
construct it. You may be able to simplify it quite a bit if some of the fields
you are searching against ALWAYS have data (never null).

For instance if [Tech] is a text value and is never null the can change the last
statement to
And Table2.Tech Like Nz([Forms]![SearchForm]![Tech],"*")

And perhaps the IDNumber condition could be changed to (assuming this is a
number field):
And Table2.IdNumber = NZ([Forms]![SearchForm]![IDNumber],Table2.IdNumber)
Table2.IDNumber will never be null in the query since you are using it in an
INNER JOIN clause

SELECT [Table1].Region
, [Table2].[IDNumber]
, [Table2].[InstallDate]
, [Table2].[StartDate]
, [Table2].[StartDate]
, [Table2].[ EndDate] <<<<<
, [Table2].Place
, [Table2].PM
, [Table2].[Tech]

FROM [Table2] INNER JOIN [IDNumber] <<<<<This should be [Table1]
ON [Table2].[IDNumber] = [Table1].[IDNumber]

WHERE (([Table1].Region = [Forms]![SearchForm]![Region] OR
[Forms]![SearchForm]![Region] IS NULL)
AND ([Table2].[IDNumber] =[Forms]![SearchForm]![ IDNumber] OR
[Forms]![SearchForm]![ IDNumber] IS NULL)
AND ([Table2].[InstallDate]= [Forms]![SearchForm]![ InstallDate] OR
[Forms]![SearchForm]![ InstallDate] IS NULL)
AND ([Table2].[StartDate] = [Forms]![SearchForm]![StartDate] OR
[Forms]![SearchForm]![StartDate] IS NULL)
AND ([Table2].[EndDate] = [Forms]![SearchForm]![EndDate] OR
[Forms]![SearchForm]![EndDate] IS NULL)
AND ([Table2].[Place] = [Forms]![SearchForm]![ Place] OR
[Forms]![SearchForm]![ Place] IS NULL)
AND ([Table2].PM = [Forms]![SearchForm]![ PM] OR [Forms]![SearchForm]![ PM]
IS NULL)
AND ([Table2].Tech = [Forms]![SearchForm]![ Tech] & "*" OR
[Forms]![SearchForm]![ Tech] IS NULL));

After running the following query a few times
I started getting an error "Query is too complex" .
Would you happen to know why this would work a few times, then get this error?
Thanks

SELECT [Table1].Region, [Table2].[IDNumber], [Table2].[InstallDate],
[Table2].[StartDate], [Table2].[StartDate], [Table2].[ EndDate],
[Table2].Place, [Table2].PM, [Table2].[Tech] FROM [Table2] INNER JOIN
[IDNumber] ON [Table2].[IDNumber] = [Table1].[IDNumber]
WHERE (([Table1].Region = [Forms]![SearchForm]![Region] OR
[Forms]![SearchForm]![Region] IS NULL)
AND ([Table2].[IDNumber] =[Forms]![SearchForm]![ IDNumber] OR
[Forms]![SearchForm]![ IDNumber] IS NULL)
AND ([Table2].[InstallDate]= [Forms]![SearchForm]![ InstallDate] OR
[Forms]![SearchForm]![ InstallDate] IS NULL)
AND ([Table2].[StartDate] = [Forms]![SearchForm]![StartDate] OR
[Forms]![SearchForm]![StartDate] IS NULL)
AND ([Table2].[EndDate] = [Forms]![SearchForm]![EndDate] OR
[Forms]![SearchForm]![EndDate] IS NULL)
AND ([Table2].[Place] = [Forms]![SearchForm]![ Place] OR
[Forms]![SearchForm]![ Place] IS NULL)
AND ([Table2].PM = [Forms]![SearchForm]![ PM] OR [Forms]![SearchForm]![ PM]
IS NULL)
AND ([Table2].Tech = [Forms]![SearchForm]![ Tech] & "*" OR
[Forms]![SearchForm]![ Tech] IS NULL));