David McAfee
davidmcafee at gmail.com
Fri Oct 1 15:11:19 CDT 2010
The Sample that you sent me works great!
Thanks.
I'd still like to get it working on a continuous form (or datasheet),
but as for now the Listbox will make it simple enough to read.
I think using the recordset .Fields.Count like you did will help me out.
Thanks again,
David
On Fri, Oct 1, 2010 at 12:43 PM, Kenneth Saylor <saylork at ipfw.edu> wrote:
> Not sure if this is what you're after.
>
> I made a table from your second to last data. Then I created two queries.
> One is of the table data, the other computes the column values.
>
> The first query feeds a listbox , the second is used in a function to find
> column sums equaling zero.
>
> Here is the second query:
> SELECT Sum(QTest.DlrPay) AS SDlrPay, Sum(QTest.SvcMgr) AS SSvcMgr,
> Sum(QTest.PartsDept) AS SPartsDept, Sum(QTest.SvcAdv) AS SSvcAdv,
> Sum(QTest.Contest) AS SContest, Sum(QTest.SvcDept) AS SSvcDept,
> Sum(QTest.SvcTech) AS SSvcTech
> FROM QTest;
> This is the function:
>
> 'Use query to find zero columns. Starting at the last query field,
> 'replace the current width with a computed width; a default value for
> non-zero
> 'or zero otherwise. The new box width is also calculated and returned
> Public Function SetColWidths(ByVal ctl As Control) As String
> Dim rs As DAO.Recordset
> Dim CurrWidths As Variant
> Dim CurrFld As Single
> Dim Cntr As Long
> Dim Pntr As Long
> Dim ListWidth As Long
>
> Const DEFAULT_WIDTH As String = "576"
>
> CurrWidths = Split(ctl.ColumnWidths, ";")
> Pntr = UBound(CurrWidths)
> Set rs = CurrentDb.OpenRecordset("qryColumnWidth")
> With rs
> For Cntr = .Fields.Count - 1 To 0 Step -1
> CurrFld = CSng(.Fields(Cntr))
> If CurrFld = 0 Then
> CurrWidths(Pntr) = "0"
> Else
> CurrWidths(Pntr) = DEFAULT_WIDTH
> End If
> ListWidth = ListWidth + CLng(CurrWidths(Pntr))
> Pntr = Pntr - 1
> Next
>
> For Cntr = Pntr To 0 Step -1
> ListWidth = ListWidth + CurrWidths(Pntr)
> Next
>
> SetColWidths = Join(CurrWidths, ";") & "|" & CStr(ListWidth)
> End With
> End Function
>
> The code behind the control is:
> Private Sub Form_Current()
> Dim WidthRet As String
> Dim Pntr As Long
>
> With Me
> WidthRet = SetColWidths(.lstTest)
> Pntr = InStr(WidthRet, "|")
> .lstTest.ColumnWidths = Left$(WidthRet, Pntr - 1)
> .lstTest.Width = CLng(Mid$(WidthRet, Pntr + 1))
> End With
> End Sub
>
> This function automatically adjusts to any number of fields.
>
> HTH (and is what you need). : )
>
> P.S. I'm sending a sample DB directly to you.
>
> Kenneth Saylor
> Chemistry Maintenance
> Indiana-Purdue University Fort Wayne
>>>> David McAfee <davidmcafee at GMAIL.COM> 10/1/2010 1:22 PM >>>
> (Crossposted on AccessD)
>
>
> I'm working in an ADP.
>
> I have the pseudo pivot table since I am working in SQL2000 (PIVOT
> wasn't available until SQL2005)
>
> SELECT
> SplitID, IncDetID, SplitTypeID, SplitAmt
> FROM tblSplit
> WHERE IncDetID = 5199
>
> returns
>
> SplitID IncDetID SplitTypeID SplitAmt
> 36 5199 1 15.00
> 37 5199 7 5.00
>
>
> My pseudo pivot table:
> SELECT IncDetID,
> SUM(CASE SplitTypeID WHEN 1 THEN SplitAmt ELSE 0 END) AS
> DlrPay,
> SUM(CASE SplitTypeID WHEN 2 THEN SplitAmt ELSE 0 END) AS
> SvcMgr,
> SUM(CASE SplitTypeID WHEN 3 THEN SplitAmt ELSE 0 END)
> AS PartsDept,
> SUM(CASE SplitTypeID WHEN 4 THEN SplitAmt ELSE 0 END) AS
> SvcAdv,
> SUM(CASE SplitTypeID WHEN 5 THEN SplitAmt ELSE 0 END) AS
> SvcDept,
> SUM(CASE SplitTypeID WHEN 6 THEN SplitAmt ELSE 0 END) AS
> SvcTech,
> SUM(CASE SplitTypeID WHEN 7 THEN SplitAmt ELSE 0 END) AS
> Contest
> FROM tblSPlit
> GROUP BY IncDetID
>
> returns:
>
> IncDetID DlrPay SvcMgr PartsDept SvcAdv SvcDept SvcTech Contest
> 5199 15.00 0.00 0.00 0.00 0.00 0.00
> 5.00
>
>
> In the actual final display, there are many tables joined to this, so
> the resultset really looks like:
>
> CustNo InvNo ItemNo Qty UnitPrice IncentiveAmt TotalIncentive
> DlrPay SvcMgr PartsDept SvcAdv SvcDept SvcTech Contest
> 07235 5452 02951 6 54.95 20.00 120.00 15.00 0.00 0.00 0.00
> 0.00 0.00 5.00
> 07235 5452 03111 12 40.95 17.00 204.00 15.00 0.00 0.00 0.00
> 0.00 0.00 2.00
> 07235 5452 01121 24 30.95 20.00 480.00 15.00 0.00 0.00 0.00
> 0.00 0.00 5.00
> 07235 5452 01161 12 36.95 25.00 300.00 15.00 0.00 0.00 0.00
> 0.00 0.00 10.00
> 07235 5452 06011 12 47.95 22.00 264.00 15.00 0.00 0.00 0.00
> 0.00 0.00 7.00
> 07235 5452 10521 12 41.95 19.00 228.00 15.00 0.00 0.00 0.00
> 0.00 0.00 4.00
>
> I'd really like it to look like this:
> CustNo InvNo ItemNo Qty UnitPrice IncentiveAmt TotalIncentive DlrPay
> Contest
> 07235 5452 02951 6 54.95 20.00 120.00 15.00 5.00
> 07235 5452 03111 12 40.95 17.00 204.00 15.00 2.00
> 07235 5452 01121 24 30.95 20.00 480.00 15.00 5.00
> 07235 5452 01161 12 36.95 25.00 300.00 15.00 10.00
> 07235 5452 06011 12 47.95 22.00 264.00 15.00 7.00
> 07235 5452 10521 12 41.95 19.00 228.00 15.00 4.00
>
> I'm still debating on displaying this as a listbox or subform on the main
> form.
>
> I'm thinking if it is in a listbox, simply loop through the last 7
> colums. If the entire column is 0 then make that column a 0" width in
> the list box.
> If I go with a datasheet subform, run a recordset clone and if all
> values for a given field are 0 then make that field hidden.
>
> any ideas?
>
> I have to do this on a form and on a report (print out of the form) so
> I'm trying to think of a good way to do this that will apply to both.
>
> Thanks,
> David
>
>
>