Jumat, 05 April 2013

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

 

Duane-

I don't see how that would work. You can certainly add a line control, but
it won't appear on top of the subreport. Subreport controls are always "on
top," so any line you add to the outer report will always disappear behind
the subreport control.

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)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Abdul Shakeel
Sent: Friday, April 05, 2013 6:39 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (16)
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar