I seem to have had trouble posting my thread - if I can't work a forum, what do I think I'm doing in Access????

Anyways I'll try again,

I have an Access db plugged into our main accounting db via ODBC. When I run a report that appears to be too big, Access will crash. The first time(s) this happened, I beleive the error message said something about the ODBC, but now it just says encountered an error.

I say the report appears to be too big because if I run the same report with tighter criteria (date range in this case), it will work. In this case if I use the whole month of June, it will crash. I can run the whole month of June with another limiter (an individual salesman's activity in June) and be successful.

I have tried to split the month up to see if there is any corrupt/incorrect data. No luck. I can run June 1 to about June 18/19 and backwards I can run June 30 to June 9/10. If I add one more day to either report - kaboom.

The query that the report is based on will run fine for June (or even longer periods). June results for the query are only 12.2k records.

I'm going to keep looking for any corrupt/incorrect data that could be mussing this up but otherwise, I was wondering if anyone was aware of this problem, or more importantly, a solution.

If you can help me out, I promise to light a candle for you.

tnx

What is the SQL for the QUERY?

Also, are you using any domain aggregates (DSum, DMax, etc.) or other Functions in the query?

I built the query in wizard, so nothing fancy involved, no code, no SQL, no VBA, no bells, no whistles.

I have an initial query that compiles the needed data from the relevant linked tables. I then have queries built on this query that have different criteria (date range, salesman, customer, invoice etc). This allows for different reports for different users, but still have some symmetry in reporting and results as well as gives me a single edit point.

I do have a couple of formulas in the initial query, just some mutiplication and some IF statements.

I had a gunslinger in the other day to have a look. He applied some exclusionary logic - if we remove this, what happens. He removed some results from a footer and voila. the report ran fine. Luckily, that footer wasn't required for that report, it was one that showed summary on a higher level. I had just kept it in for symmetry.

Soooooo, I'm wondering if it has more to do with my computer than with Access. If the report ran fine with smaller data set -tighter date range or the other reports that could be run for the same periodbut with other limits for salesman etc, and also ran fine after leaving out that footer (which is in the other similar reports), I'm thinking I have to ask my IT dept for more RAM or better processor (dammit Scotty, I need more power ).

Anyway, If you can glean anything else from my rambling that may help me, it would be appreciated, otherwise I am going to consider this one fixed, although not fully explained.

tnx

I built the query in wizard, so nothing fancy involved, no code, no SQL, ...
Umm, ALL queries are SQL statements. The QBE grid just displays them in a graphical way. Open the query into design view and then go to VIEW > SQL View and then you can copy and paste the SQL statement that makes up your query.

Yeah, sorry. I realize that after rereading what I wrote. I was thinking along the lines that you were asking if I did anything special.

If you'd like to see the SQL my initial looks thusly:

SELECT dbo_uv_SalesInvoiceHeader.Company, dbo_uv_SalesInvoiceLine.[Document No_], dbo_uv_SalesInvoiceHeader.[Posting Date], dbo_uv_SalesInvoiceLine.[Sell-to Customer No_], dbo_uv_SalesInvoiceHeader.[Sell-to Customer Name], dbo_uv_SalesInvoiceLine.No_, dbo_uv_SalesInvoiceLine.Quantity, [Quantity]*1 AS Expr1, CSng([Quantity]) AS Qty, dbo_uv_SalesInvoiceLine.[Shipment Date], dbo_uv_SalesInvoiceLine.[Gen_ Bus_ Posting Group], dbo_uv_SalesInvoiceLine.Type, dbo_uv_SalesInvoiceLine.Description, dbo_uv_SalesInvoiceLine.[Gen_ Prod_ Posting Group], dbo_uv_SalesInvoiceLine.[Unit Price], IIf([dbo_uv_salesInvoiceLine]![type]=2,[Unit Price]*[dbo_uv_SalesInvoiceLine]![Quantity],0) AS Expr6, IIf([dbo_uv_SalesInvoiceLine]![type]=2,[Unit Price]*0.36*[Quantity],0) AS Expr2, IIf([dbo_uv_SalesInvoiceLine]![Type]=2,0,[dbo_uv_SalesInvoiceLine]![Amount]*1) AS Expr4, IIf([dbo_uv_salesInvoiceLine]![type]=1,0,IIf([dbo_uv_SalesInvoiceHeader]![Shipment Method Code]="charge",[Expr6]*0.03,IIf([dbo_uv_SalesInvoiceHeader]![Shipment Method Code]="prepaid",[expr6]*0.03,0))) AS Expr5, IIf([dbo_uv_SalesInvoiceLine]![Type]=1,0,[dbo_uv_SalesInvoiceLine]![Amount]*1) AS Expr3, dbo_uv_SalesInvoiceHeader.[Shipment Method Code], chain.[Rebate level], dbo_uv_Customer.[Chain Name], chain.[RL start], chain.[RL end], -([expr3]+IIf([expr4]<0,[expr4],0))*[Rebate level] AS Expr7, dbo_uv_SalesInvoiceHeader.[Salesperson Code], [Expr3]+[Expr4]+[Expr7] AS Expr8, [Expr2]+[Expr5] AS Expr9
FROM ((dbo_uv_SalesInvoiceLine INNER JOIN dbo_uv_SalesInvoiceHeader ON (dbo_uv_SalesInvoiceLine.Company = dbo_uv_SalesInvoiceHeader.Company) AND (dbo_uv_SalesInvoiceLine.[Document No_] = dbo_uv_SalesInvoiceHeader.No_)) INNER JOIN dbo_uv_Customer ON (dbo_uv_SalesInvoiceHeader.Company = dbo_uv_Customer.Company) AND (dbo_uv_SalesInvoiceHeader.[Sell-to Customer No_] = dbo_uv_Customer.No_)) LEFT JOIN chain ON dbo_uv_Customer.[Chain Name] = chain.Chain
WHERE (((dbo_uv_SalesInvoiceLine.[Sell-to Customer No_])<>"r001") AND ((dbo_uv_SalesInvoiceLine.[Shipment Date])>#1/1/1900#) AND ((chain.[RL start])<[posting date]) AND ((chain.[RL end])>[posting date] Or (chain.[RL end]) Is Null) AND ((dbo_uv_SalesInvoiceHeader.[Salesperson Code]) Is Not Null Or (dbo_uv_SalesInvoiceHeader.[Salesperson Code])<>"bh" Or (dbo_uv_SalesInvoiceHeader.[Salesperson Code])<>""));


and the subsequent one as:
SELECT [sales detail qry AlcoCan].Company, [sales detail qry AlcoCan].[Document No_], [sales detail qry AlcoCan].[Posting Date], [sales detail qry AlcoCan].[Sell-to Customer No_], [sales detail qry AlcoCan].[Sell-to Customer Name], [sales detail qry AlcoCan].No_, [sales detail qry AlcoCan].Expr1, [sales detail qry AlcoCan].[Shipment Date], [sales detail qry AlcoCan].[Gen_ Bus_ Posting Group], [sales detail qry AlcoCan].Type, [sales detail qry AlcoCan].Description, [sales detail qry AlcoCan].[Gen_ Prod_ Posting Group], [sales detail qry AlcoCan].[Unit Price], [sales detail qry AlcoCan].Expr6, [sales detail qry AlcoCan].Expr2, [sales detail qry AlcoCan].Expr4, [sales detail qry AlcoCan].Expr5, [sales detail qry AlcoCan].Expr3, [sales detail qry AlcoCan].[Shipment Method Code], [sales detail qry AlcoCan].[Rebate level], [sales detail qry AlcoCan].[Chain Name], [sales detail qry AlcoCan].[RL start], [sales detail qry AlcoCan].[RL end], [sales detail qry AlcoCan].Expr7, [sales detail qry AlcoCan].[Salesperson Code], [sales detail qry AlcoCan].Expr8, [sales detail qry AlcoCan].Expr9
FROM [sales detail qry AlcoCan]
WHERE ((([sales detail qry AlcoCan].[Posting Date]) Between [Forms]![Title Page]![Start Date] And [Forms]![Title Page]![End Date]));

by the by, the gunslinger showed me how to change the EXPR## to something more legible. never knew I could write in there, always tried through properties sheet where my logic told me to look