Selecting columns for display based on user criterion in MSAccess

0
votes

I have an access table with columns

Account Id, account description, GL Code, year, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec. The months columns holds the amount.

I want if user selects Jan on Form than Account Id, account description, Gl code,year and Jan gets displayed. Similarly if user selects Feb than Account Id account description, gl code, year and Feb gets displayed. Similarly for other months.

Have no idea on how to do this. To my knowledge we can filter rows but not the columns.

Please suggest if any possible solution. Please note that I cannot change the database design as the data is getting downloaded from ERP package.

1
ms-accessvbams-access-2010ms-access-2007
Possible duplicate of How to make a vba code in a form that show/hides query columns? ( MS- Access) – Erik A

1 Answers

1
votes

Create a query where you set the SQL dynamically:

Dim qd As DAO.QueryDef
Dim SQL As String
Dim MonthName As String

MonthName = "Feb"  ' Month to search.

Set qd = CurrentDb.QueryDefs("YourQuery")
SQL = "Select [GL Code], [year], [" & MonthName & "] As Amount From YourTable"
qd.SQL = SQL

Now, open the query or open a recordset using the query as source.

SQL only:

Select 
    [GL Code], 
    [year], 
    IIf(MonthName = 'Jan', [Jan],
    IIf(MonthName = 'Feb', [Feb],
    IIf(MonthName = 'Mar', [Mar],
    IIf(MonthName = 'Apr', [Apr],
    IIf(MonthName = 'May', [May],
    IIf(MonthName = 'Jun', [Jun],
    IIf(MonthName = 'Jul', [Jul],
    IIf(MonthName = 'Aug', [Aug],
    IIf(MonthName = 'Sep', [Sep],
    IIf(MonthName = 'Oct', [Oct],
    IIf(MonthName = 'Nov', [Nov],
    IIf(MonthName = 'Dec', [Dec])))))))))))) As Amount
From 
    YourTable