Query Is Too Complex

[ashg657]

Hey people.
Am getting that very annoying "query is too complex" error come up on my
database.
Unfortunately I cannot post all the SQL just yet as I am at home and my
database is at work. Apologies for this will post it up asap.
We have emptied out our database, it works fine on 30 other PC's but there
is 1 and 1 only which brings up this error when running a particular select
query to generate a report.
Why would it only come up on one of the PC's and not all of them? Any ideas?
Because it has only recently been emptied the query is only selecting
results from a series of tables in which the main table only, at the moment,
contains 150 records.

I am completely baffled as to why this is happening. Here's what I have
tried so far to rectify the problem:

+ Import a new query from one of the other databases from a known working one
+ Checked data in the tables within the query for erroneous symbols or
characters
+ Checked the system hardware capabilities

I then found that it would occassionally throw up a different error, this
time saying "System Resource Exceeded" well this just serves to confuse me
further. The system resources are plentiful. 512MB RAM , 2.6GHZ Processor.

So yeh I guess I am completely baffled on this one. Please help!

Thank-You.
Ash.

 

[ashg657]

Few things I missed out in the original posting....

I copied the faulty database onto a flash drive and put it onto my personal
laptop and my work colleagues laptop and guess what, the problem didn't exist.
Also,
We have installed Office 2000 service packs and WindowsXP is all updated.

It's just this one damn PC, why on earth would it do this? Giving you as
much info as possible on this one so hopefully someone can assist me here.

Many thanks.
Ash.

 

[John W. Vinson]

Why would it only come up on one of the PC's and not all of them? Any ideas?

This appears to be the very common References bug. Open any module in design
view on the affected computer, or open the VBA editor by typing Ctrl-G. Select
Tools... References from the menu. One of the .DLL files required by Access
will probably be marked MISSING. Uncheck it, recheck it, close and open
Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

John W. Vinson [MVP]

 

[kelvin.anderson]

This appears to be the very common References bug. Open any module in design
view on the affected computer, or open the VBA editor by typing Ctrl-G. Select
Tools... References from the menu. One of the .DLL files required by Access
will probably be marked MISSING. Uncheck it, recheck it, close and open
Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

John W. Vinson [MVP]

I'm seeing a similar problem. I didn't find any missing DLLs, so I
checked a reference, closed and opened Access, and unchecked the
reference per John's recommendation. I still get "too complex"
message. My particular query has been working for some time, and just
stopped working today while I was testing changes on a form. I'm
working with Access 2007 on Windows Vista.

 

[kelvin.anderson]

I'm seeing a similar problem. I didn't find any missing DLLs, so I
checked a reference, closed and opened Access, and unchecked the
reference per John's recommendation. I still get "too complex"
message. My particular query has been working for some time, and just
stopped working today while I was testing changes on a form. I'm
working with Access 2007 on Windows Vista.- Hide quoted text -

- Show quoted text -

Here is the SQL statement that is not working. The problem appears to
occur only when I try to subtract 1 from the DepartureDate or the
Checkout fields, because it runs OK if I remove the "subtract 1" from
the query.

SELECT [tblBookings].[RoomID], [tblBookings].[Arrival], [tblBookings].
[Checkout]
FROM tblBookings
WHERE (tblBookings.BookingStatus<>"Cancelled") And
(((tblBookings.Arrival) Between Forms!frmReservation!ArrivalDate And
(Forms!frmReservation![DepartureDate]-1))) Or
((([tblBookings.Checkout]-1) Between Forms!frmReservation!ArrivalDate
And Forms!frmReservation!DepartureDate)) Or
(((tblBookings.Arrival)<Forms!frmReservation!ArrivalDate) And
(([tblBookings.Checkout]-1)>(Forms!frmReservation![DepartureDate]-1)));

 

[John Spencer]

Try using

DateAdd("d",-1,Forms!frmReservation![DepartureDate])

It could be that the reference to the value of the control on the form
is not being recognized as a date.




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I'm seeing a similar problem. I didn't find any missing DLLs, so I
checked a reference, closed and opened Access, and unchecked the
reference per John's recommendation. I still get "too complex"
message. My particular query has been working for some time, and just
stopped working today while I was testing changes on a form. I'm
working with Access 2007 on Windows Vista.- Hide quoted text -

- Show quoted text -

Here is the SQL statement that is not working. The problem appears to
occur only when I try to subtract 1 from the DepartureDate or the
Checkout fields, because it runs OK if I remove the "subtract 1" from
the query.

SELECT [tblBookings].[RoomID], [tblBookings].[Arrival], [tblBookings].
[Checkout]
FROM tblBookings
WHERE (tblBookings.BookingStatus<>"Cancelled") And
(((tblBookings.Arrival) Between Forms!frmReservation!ArrivalDate And
(Forms!frmReservation![DepartureDate]-1))) Or
((([tblBookings.Checkout]-1) Between Forms!frmReservation!ArrivalDate
And Forms!frmReservation!DepartureDate)) Or
(((tblBookings.Arrival)<Forms!frmReservation!ArrivalDate) And
(([tblBookings.Checkout]-1)>(Forms!frmReservation![DepartureDate]-1)));