ACCESS FEED: RE: [MS_AccessPros] Print fixed height vertical lines in detail section

Kamis, 04 April 2013

RE: [MS_AccessPros] Print fixed height vertical lines in detail section

I'm still not sure I totally understand. However if this was the Northwind and you wanted the detail section(s) to display 10 possible order details regardless of how many actual detail records existed for the order... I would base the report on the orders table and use a subreport based on the order details. Set the height of the order details subreport to a specific height and don't allow it to grow or shrink. You can then add lines to the main report that span the entire height of the 10 possible order details.

Duane Hookom MVP
MS Access

----------------------------------------
> To: MS_Access_Professionals@yahoogroups.com
> From: JohnV@msn.com
> Date: Thu, 4 Apr 2013 14:33:00 +0200
> Subject: RE: [MS_AccessPros] Print fixed height vertical lines in detail section
>
> Abdul-
>
>
>
> It's a bit tricky. Here's what I did in Northwind 2007:
>
>
>
> 1) Create ztblReportLines and insert 15 numbered rows
>
> 2) Created a new query called Invoice Data Filter:
>
> PARAMETERS [Forms]![Order Details]![Order ID] Long;
>
> SELECT [Forms]![Order Details]![Order ID] AS OrderNo, Orders.[Order ID],
> Orders.[Ship Name], Orders.[Ship Address], Orders.[Ship City], Orders.[Ship
> State/Province], Orders.[Ship ZIP/Postal Code], Orders.[Ship
> Country/Region], Orders.[Customer ID], Customers.Company AS [Customer Name],
> Customers.Address, Customers.City, Customers.[State/Province],
> Customers.[ZIP/Postal Code], Customers.[Country/Region], [Employees
> Extended].[Employee Name] AS Salesperson, Orders.[Order Date],
> Orders.[Shipped Date], Shippers.Company AS [Shipper Name], [Order
> Details].[Product ID], Products.ID AS [Product ID], [Order Details].[Unit
> Price], [Order Details].Quantity, [Order Details].Discount, CCur(Nz([Unit
> Price]*[Quantity]*(1-[Discount]),0)/100)*100 AS ExtendedPrice,
> Orders.[Shipping Fee], CLng(DCount("*","[Order Details]","[Product ID] <= "
> & NZ([Product ID],0) & " And [Order ID] = " & NZ([Orders].[Order ID],0))) AS
> LineNo, Products.[Product Name]
>
> FROM (Shippers RIGHT JOIN (Customers RIGHT JOIN (Orders LEFT JOIN [Employees
> Extended] ON Orders.[Employee ID] = [Employees Extended].ID) ON Customers.ID
> = Orders.[Customer ID]) ON Shippers.ID = Orders.[Shipper ID]) LEFT JOIN
> ([Order Details] LEFT JOIN Products ON [Order Details].[Product ID] =
> Products.ID) ON Orders.[Order ID] = [Order Details].[Order ID]
>
> WHERE (((Orders.[Order ID])=[Forms]![Order Details]![Order ID]));
>
>
>
> Note that in the sample database, the report opens with a filter on Order
> ID, but to get the LEFT JOIN to work in the following query, I had to add a
> parameter pointing to the form that's always open and use that to generate
> Order ID in all lines - even the "dummy " ones because the report is grouped
> on Order ID.
>
>
>
> 3) Create a new query called Invoice Data Line Nos:
>
> SELECT ztblReportLines.LineNo, [Invoice Data Filter].OrderNo AS [Order ID],
> [Invoice Data Filter].[Ship Name], [Invoice Data Filter].[Ship Address],
> [Invoice Data Filter].[Ship City], [Invoice Data Filter].[Ship
> State/Province], [Invoice Data Filter].[Ship ZIP/Postal Code], [Invoice Data
> Filter].[Ship Country/Region], [Invoice Data Filter].[Customer ID], [Invoice
> Data Filter].[Customer Name], [Invoice Data Filter].Address, [Invoice Data
> Filter].City, [Invoice Data Filter].[State/Province], [Invoice Data
> Filter].[ZIP/Postal Code], [Invoice Data Filter].[Country/Region], [Invoice
> Data Filter].Salesperson, [Invoice Data Filter].[Order Date], [Invoice Data
> Filter].[Shipped Date], [Invoice Data Filter].[Shipper Name], [Invoice Data
> Filter].[Product ID], [Invoice Data Filter].[Unit Price], [Invoice Data
> Filter].Quantity, [Invoice Data Filter].Discount, [Invoice Data
> Filter].ExtendedPrice, [Invoice Data Filter].[Shipping Fee], [Invoice Data
> Filter].LineNo, [Invoice Data Filter].[Product Name]
>
> FROM ztblReportLines LEFT JOIN [Invoice Data Filter] ON
> ztblReportLines.LineNo = [Invoice Data Filter].LineNo;
>
>
>
> If you open the Order Details form and move to an order that has an invoice,
> then open the above query, you should see the data lines from the order
> details plus "n" blank lines depending on how many rows you defined in
> ztblReportLines.
>
>
>
> 4) Modified the Invoice report so that it can open without a filter (remove
> the Embedded Macro in the Open event) to test it. Changed the sort on
> Product ID to descending to put all the "blank" lines at the end, and
> modified the expresson for the txtExtendedPrice control to:
> =IIf(IsNull([Product ID]),Null,[ExtendedPrice]) .. to clear out the $0.00
> display for the dummy lines.
>
>
>
> Report now always prints 15 lines per order regardless of the number of
> products.
>
>
>
> John Viescas, Author
>
> Microsoft Access 2010 Inside Out
>
> Microsoft Access 2007 Inside Out
>
> Microsoft Access 2003 Inside Out
>
> Building Microsoft Access Applications
>
> SQL Queries for Mere Mortals
>
> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Abdul Shakeel
> Sent: Thursday, April 04, 2013 1:00 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Print fixed height vertical lines in detail
> section
>
>
>
>
>
> On Thu, Apr 4, 2013 at 3:31 PM, John Viescas <JohnV@msn.com
> <mailto:JohnV%40msn.com> > wrote:
>
> > qryReportInventory
>
> Dear John,
>
> Very thank full to you for this help, but I could not employ this in my
> database for some reasons, I threrfore requested you to use these queries
> in Northwind sample database & upload in the file section of the group it
> will also helpful to others out there
>
> --
> Regards,
> Abdul Shakeel
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>