[AccessD] Is there a way to dynamically hide fields if needed

Charlotte Foust charlotte.foust at gmail.com
Fri Oct 1 10:33:09 CDT 2010


Hiding a column that contains all zero values may seem like a good
idea, but wouldn't it be confusing to users if a column sometimes
appeared and sometimes vanished?

Charlotte Foust

On Thu, Sep 30, 2010 at 5:31 PM, David McAfee <davidmcafee at gmail.com> wrote:
> 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    Unit Price    IncentiveAmt
> TotalIncentive    DlrPay    SvcMgr    PartsDept   SvcAdv   SvcDept
> SvcTech   Contest
> ------------ --------------- ------------- -----------
> ---------------- --------------------- ----------------------
> ------------- -------------- ---------------- -------------
> -------------- -------------- --------------
> 07235     54521337   02951         6          54.95              20.00
>          120.00      15.00           0.00        0.00           0.00
>   0.00           0.00         5.00
> 07235     54521337   03111        12         40.95              17.00
>         204.00      15.00           0.00        0.00           0.00
>  0.00           0.00         2.00
> 07235     54521337   01121        24         30.95              20.00
>         480.00      15.00           0.00        0.00           0.00
>  0.00           0.00         5.00
> 07235     54521337   01161        12         36.95              25.00
>         300.00      15.00           0.00        0.00           0.00
>  0.00           0.00       10.00
> 07235     54521337   06011        12         47.95              22.00
>         264.00      15.00           0.00        0.00           0.00
>  0.00           0.00         7.00
> 07235     54521337   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    Unit Price    IncentiveAmt
> TotalIncentive    DlrPay    Contest
> ------------ --------------- ------------- -----------
> ---------------- --------------------- ----------------------
> -------------  --------------
> 07235     54521337   02951         6          54.95              20.00
>          120.00      15.00         5.00
> 07235     54521337   03111        12         40.95              17.00
>         204.00      15.00         2.00
> 07235     54521337   01121        24         30.95              20.00
>         480.00      15.00         5.00
> 07235     54521337   01161        12         36.95              25.00
>         300.00      15.00       10.00
> 07235     54521337   06011        12         47.95              22.00
>         264.00      15.00         7.00
> 07235     54521337   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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>




More information about the AccessD mailing list