Strange Issue with a Query

[junker1234]

Hi
I'm a complete novice when it comes to Access. I have been working on
getting a database together and overall it has been going well thanks
in large part to the help that I have received from people on this
group.

The other day I completed my final query that I have to do before I can
finish this project. It simply queries 4 tables gets some fields from
them and does some functions with that data (DMAX, DMIN, etc).

The query worked the first several times that I used it but now I get
an error message "Cannot open any more tables." When I get this
message I have to ctrl-alt-del out of Access. All other queries run
fine.

I looked online for help regarding this issue and it seemed that
everything I could find referred to either antiquated versions of
Access or involved parts of Access that I am not using (and don't even
know what they are) like Subforms.

I am using Access 2002 and my Jet software is up to date. I am not
using any subforms that I know of.

I am truly at a loss for what to do because I don't adequately
understand the TableID issues to work this out.

The only form that I use at all is a switchboard which opens with the
file. It has 3 command buttons. I tried deactivating the switchboard
on startup thinking it might be tying up tables somehow but it made no
difference and I received the same error.

Am I doing something wrong? My basic queries can't possibly be running
up against a limitation of Access can they?

Thanks a ton for any guidance.

 

[Guest]

It would help if you post your SQL code so that it could be looked at (while
viewing query in design mode - Menu>view>sql view)

James

 

[junker1234]

Here it is:

SELECT DISTINCT POs.Date, POs.Memo, POs.[Deliv Date], POs.Num,
POs.Name, POs.FOB, POs.Via, POs.[Due Date], POs.[qb by], POs.[date of
last revision], POs.Item, POs.Qty, POs.[Source Name], POs.[Rcv'd],
VendorDays.Vendor, VendorDays.[Days until Ready], VendorDays.[will
call], Sales.*, [POs.Date]+[Days until Ready] AS EstBuiltDate,
IIf([POs.Date]<>[Deliv Date],[Deliv Date],[EstBuiltDate]) AS
SelectMachineorConf, Weekday([SelectMachineorConf]) AS WeekdayNum,
IIf([Source Name]="Equipment Distributors
Inc",Choose(1,0,0,0,0,0,2),IIf([Source Name]="Desk
Makers",Choose([WeekdayNum],1,0,1,0,4,3,2),IIf([will
call],Choose([WeekdayNum],4,3,2,1,0,6,5),0))) AS RoundUp,
Format(DateAdd('d',[RoundUp],[SelectMachineorConf]),"Short Date") AS
FinalExp, Sales.[S# O# #], [POs.Num] AS [Number],
DSum("[WholeRow]","delivsumm","[InvNumber] ='" & [Sales.Num] & "'") AS
[Count], IIf(Fix([POs.date of last revision])<>[POs.date of last
revision],0,[POs.Qty]) AS WholeRow, [Sales.Num] AS InvNumber,
DMax("[FinalExp]","delivsumm","[InvNumber] ='" & Sales.Num & "'") AS
GroupDate, DMin("[FinalExp]","delivsumm","[InvNumber] ='" & Sales.Num &
"'") AS GroupMin, ZipCode.*

FROM ZipCode RIGHT JOIN ((POs RIGHT JOIN Sales ON POs.[FOBdate of last
revision] = Sales.Numcost) LEFT JOIN VendorDays ON POs.[Source Name] =
VendorDays.Vendor) ON ZipCode.zip = Sales.[Name Zip]

WHERE (((POs.Date)>DateAdd('d',-70,Date())) AND ((Sales.[S# O# #]) Is
Null));


Thanks again for any help

 

[Guest]

Probably your best bet is to eliminate the complex pieces of the query until
the problem goes away, then you will have some place to start. You have
several complex pieces which make up the query (iif, choose, Dmin, Dsum). Get
it to a point where it works, then begin adding pieces back in until it
breaks.

James

 

[junker1234]

Thanks for the advice, I'll give it a try.

Is this a common issue with the more complex functions?

 

[junker1234]

The only way that the query will run is if I get rid of all DMAX, DMIN,
and CHOOSE fields. This defeats the purpose of my query.

Does it seem reasonable to you that what I'm doing is overloading
Access?

Being the novice that I am, could there be something obvious that I'm
overlooking?

 

[Guest]

Is this statment correct?
Choose(1,0,0,0,0,0,2)
It doesn't look like there is something to choose from.

There's enough in your query to make it hard to understand (especially since
I don't have the file layouts on my computer so I'm looking at the SQL
instead of the query format.) So I'm going to have to give general advice.

Is there any way to break the query into two queries with the second based
on the results of the first? I did this just the other day and it sped up one
of my queryies tremendously. I think it kept the overhead Access was having
to keep track of smaller.

Does the addition of even one of any of the 'Choose, Dmin, Dmax' cause the
failure?
Since you have several CHOOSE statements in a nested IIF, add only one at a
time to see when it fails.

James