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

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]