Microsoft Access - Changing the recordsource or dynamically hiding columns of a query
Asked By Jason on 26-Jun-12 01:34 PM
So my problem is that I have a query that has fields based on what month we are in. For instance, since it's June right now I would need 6 fields in this query. When it turns to July, I would want 7 fields to show. I'm not sure how to accomplish this however. I should mention this query will be used in a graph in a report. My ideas so far have to either hide the fields based on what month it is in vba or to change the recordsource of the graph in vba and make 12 queries. Anyone have any other ideas or know how to make the ideas I have become possible?
Pat Hartman replied to Jason on 28-Jun-12 08:29 PM
Selection criteria would limit the rows selected so if your data is normalized, the crosstab solution will work. It sounds like you have 12 columns in the same row rather than 12 rows so that means more work as is usual for a non-normalized schema. You either need 12 queries and decide which to run based on the month or you build the SQL string with VBA depending on the month.