Query works - Report doesnt

[kermit01de]

Hmm, I have a query that is working - and a report that bases on that query does "nothing". If I call that Report - it seems to open - but nothing happens. If I open the Query - it shows the figures.

It is Ac2K - W2K3 Server and MSSQL 2K

Any hints? Any MDAC missing perhaps?


--------------------------------------
>>>>>> Bugs will appear in one part of a working program when another 'unrelated' part is modified <<<<<

 

[MazeWorX]

can you post the code that calls the report?

HTH << MaZeWorX >> Remember amateurs built the ark - professionals built the Titanic [flush]

 

[dhookom]

Do you have a default printer defined? Have you tried changing to a different printer?

Duane
Hook'D on Access
MS Access MVP

 

[kermit01de]

Thank you, after some hours of investigation I finally found what happens (but still have no solution)

Having a query basing on several tables. In one of the result fields there is an ID referring to another table (to get the "name" linked by ID). As soon as I delete the table from the wuery, everything works fine. If I add the table and create the link (ID <-> ID) and start the query, nothing happens. I am able to CTrl-Break, but there is no progressbar, no progress even if running that query for hours.

And here is the code with and without the table "Rep_Cat_Master".

--------------------------------
SELECT dbo_Business_Day_Dates.BD_start_dttime, dbrder_Header.profit_center_id, IIf([tender_class_id]=1,dbo_order_item.sales_amt_gross-dbo_order_item.discount_amt,"0") AS CashRev, IIf([tender_class_id]=2,dbo_order_item.sales_amt_gross-dbo_order_item.discount_amt,"0") AS CompRev, Val([CashRev])+Val([CompRev]) AS TotalRev, dbrder_Header.bus_day_id, dbo_Profit_Center_Master.profit_center_name, qry_MaxMinDatum.Von, qry_MaxMinDatum.Bis, qry_MaxMinDatum.store_name, dbo_Menu_Item_Master.rev_cat_id, dbo_Revenue_Cat_Master.rev_cat_name, qry_MaxMinDatum.Version
FROM (dbo_Menu_Item_Master INNER JOIN ((((((dbo_Tender_Master INNER JOIN dbrder_Tender ON dbo_Tender_Master.tender_id = dbrder_Tender.tender_id) INNER JOIN dbrder_Header ON dbrder_Tender.order_hdr_id = dbrder_Header.order_hdr_id) INNER JOIN dbo_Business_Day_Dates ON dbrder_Header.bus_day_id = dbo_Business_Day_Dates.bus_day_id) INNER JOIN dbo_Profit_Center_Master ON dbrder_Header.profit_center_id = dbo_Profit_Center_Master.profit_center_id) INNER JOIN qry_MaxMinDatum ON (dbo_Profit_Center_Master.store_id = qry_MaxMinDatum.Filiale) AND (dbo_Profit_Center_Master.ent_id = qry_MaxMinDatum.Enterprise)) INNER JOIN dbrder_Item ON (dbrder_Header.order_hdr_id = dbrder_Item.order_hdr_id) AND (dbo_Profit_Center_Master.profit_center_id = dbrder_Item.profit_center_id)) ON dbo_Menu_Item_Master.menu_item_id = dbrder_Item.menu_item_id) INNER JOIN dbo_Revenue_Cat_Master ON dbo_Menu_Item_Master.rev_cat_id = dbo_Revenue_Cat_Master.rev_cat_id
WHERE (((dbrder_Header.bus_day_id)>=[Forms]![Datum]![Von Datum] And (dbrder_Header.bus_day_id)<=[Forms]![Datum]![Bis Datum]) AND ((dbrder_Item.void_type_id)<>1 And (dbrder_Item.void_type_id)<>3));
-------------------------------

AND WITHOUT (the working one):

-------------------------------
SELECT dbo_Business_Day_Dates.BD_start_dttime, dbrder_Header.profit_center_id, IIf([tender_class_id]=1,dbo_order_item.sales_amt_gross-dbo_order_item.discount_amt,"0") AS CashRev, IIf([tender_class_id]=2,dbo_order_item.sales_amt_gross-dbo_order_item.discount_amt,"0") AS CompRev, Val([CashRev])+Val([CompRev]) AS TotalRev, dbrder_Header.bus_day_id, dbo_Profit_Center_Master.profit_center_name, qry_MaxMinDatum.Von, qry_MaxMinDatum.Bis, qry_MaxMinDatum.store_name, dbo_Menu_Item_Master.rev_cat_id, qry_MaxMinDatum.Version
FROM dbo_Menu_Item_Master INNER JOIN ((((((dbo_Tender_Master INNER JOIN dbrder_Tender ON dbo_Tender_Master.tender_id = dbrder_Tender.tender_id) INNER JOIN dbrder_Header ON dbrder_Tender.order_hdr_id = dbrder_Header.order_hdr_id) INNER JOIN dbo_Business_Day_Dates ON dbrder_Header.bus_day_id = dbo_Business_Day_Dates.bus_day_id) INNER JOIN dbo_Profit_Center_Master ON dbrder_Header.profit_center_id = dbo_Profit_Center_Master.profit_center_id) INNER JOIN qry_MaxMinDatum ON (dbo_Profit_Center_Master.store_id = qry_MaxMinDatum.Filiale) AND (dbo_Profit_Center_Master.ent_id = qry_MaxMinDatum.Enterprise)) INNER JOIN dbrder_Item ON (dbrder_Header.order_hdr_id = dbrder_Item.order_hdr_id) AND (dbo_Profit_Center_Master.profit_center_id = dbrder_Item.profit_center_id)) ON dbo_Menu_Item_Master.menu_item_id = dbrder_Item.menu_item_id
WHERE (((dbrder_Header.bus_day_id)>=[Forms]![Datum]![Von Datum] And (dbrder_Header.bus_day_id)<=[Forms]![Datum]![Bis Datum]) AND ((dbrder_Item.void_type_id)<>1 And (dbrder_Item.void_type_id)<>3));




--------------------------------------
>>>>>> Bugs will appear in one part of a working program when another 'unrelated' part is modified <<<<<

 

[dhookom]

I would try to use a pass-through query. You could use a little DAO code to change the SQL of the p-t to filter the dates etc. This would assume qry_MaxMinDatum could be created on the SQL Server.

Duane
Hook'D on Access
MS Access MVP

 

[kermit01de]

Hmm, as I understand I should create some queries inside the SQL Server instead of doing them in Access. I think that would cause a severe problem when the Application that bases originally on the SQL Server would be updated somewhen.

The Access-Report-Tool, I did, is just an add-on to a POS-System to have some additional reports. So the POS-System-company will not care about any changes to the SQL-databases with an update.

The funny thing about it, is, that the Revenue_Cat_Master only contains 6 records that contain ID, Name, StoreID, and some few other fields.

Thank you for any hints - for a non professional Access / SQL user.

--------------------------------------
>>>>>> Bugs will appear in one part of a working program when another 'unrelated' part is modified <<<<<

 

[dhookom]

If all the data resides on your SQL Server, you can use pass-through queries that should perform multiple times faster. You wouldn't necessarily have to create views (queries) on SQL Server to accomplish this.

Your IIf() expression would need to be replaced by:

Case When Tender_class_id=1 THEN order_item.sales_amt_gross-order_item.discount_amt ELSE 0 END AS CashRev

NOTICE your IIf() should have a false return of 0, not the string "0".

Duane
Hook'D on Access
MS Access MVP