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

Dear Duane

Thanks.... I am amazed it was that simple its really works for me


On Thu, Apr 4, 2013 at 11:33 PM, Duane Hookom <duanehookom@hotmail.com>wrote:

> **
>
>
> 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
> >
> >
> >
>
>
>



--
Regards,
Abdul Shakeel


[Non-text portions of this message have been removed]



------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/