Access selecting fields based on variable

You can (re)write the SQL:

Number = 2018
SQL = "Select [" & Number & "] From YourTable"

Select allOpen in new window

Or use a RecordSet:

FieldName = "2018"
Set rs = CurrentDb.OpenRecordset("Select * From YourTable")
' ... goto or find a record and read the value of the field.
SomeValue = rs.Fields(FieldName).Value

Select allOpen in new window

Thanks for the reply Gustav.

What if I'm using a regular query, rather than VBA? How does one (re)write the SQL in this case?
I'm trying to alter an existing query which is then referred to my other queries and forms, so would prefer to keep the query if I can.

If I go to SQL view of the query then at the moment it has:

SELECT qry_UnitsByRegion.CompanyName, qry_UnitsByRegion.Region, Sum(qry_UnitsByRegion.[2018]) AS SumOfChosenYear
FROM qry_UnitsByRegion
GROUP BY qry_UnitsByRegion.CompanyName, qry_UnitsByRegion.Region;

I'd like to replace the [2018] with [TempVars!VarYear] - but doing this just generates an error.

Perhaps there is a way that you can make an existing query use a query string of your choosing (in which case you can do what you're suggesting above)?

One solution would be a small VBA method and 2 "fake" Query

SELECT qry_UnitsByRegion.CompanyName, qry_UnitsByRegion.Region, Sum(qry_UnitsByRegion.[9999]) AS SumOfChosenYear
FROM qry_UnitsByRegion
GROUP BY qry_UnitsByRegion.CompanyName, qry_UnitsByRegion.Region;

Select allOpen in new window

In the after update of the combobox

Private Sub Combo1_AfterUpdate()
Dim strSQL as String
Dim qdfSource as QueryDef
Dim qdfDestincation as QueryDef
StrSQL = vbNullstring
set qdfSource = CurrentDb.QueryDefs("NameOfFakeQuery_Source")
strSQL =qdf.SQL
strsql = Replace(strsql,"9999",Me.Combo1)
qdfSource .Close
set qdfDestination = CurrentDb.QueryDefs("NameOfFakeQuery_Destination")
qdfDestination .SQL = strSQL
qdfDestination .Close
End Sub

Select allOpen in new window

The philosophy goes by this
You have a "Source" query that you never change
You read its SQL and you change it...and that changed SQL you implant it to a Destination Query that you will use for all your needs
Think it like this
Query_Base
Query_Destination  
Change names to your Liking
So you choose a year in the combo ...and the "Destination" query gets updated and its ready for use

The real issue here is poor table design.  You should never have a field in a table where the field name is  data.  In your case, you should have a field titled, "Calendar Year" or "Fiscal Year" or something like that, and then another field to reflect the values that you are currently storing in the column (2018).

You could get around this by creating a normalizing query, something like:

SELECT Field1, Field2,  FieldN, 2015 as CalendarYear , [2015] as CYValue
FROM yourTable WHERE [2015] is NOT NULL
UNION ALL
SELECT Field1, Field2,  FieldN, 2016 as CalendarYear , [2016] as CYValue
FROM yourTable WHERE [2016] is NOT NULL
UNION ALL
SELECT Field1, Field2,  FieldN, 2017 as CalendarYear , [2017] as CYValue
FROM yourTable WHERE [2017] is NOT NULL
UNION ALL
SELECT Field1, Field2,  FieldN, 2018 as CalendarYear , [2018] as CYValue
FROM yourTable WHERE [2018] is NOT NULL
UNION ALL
SELECT Field1, Field2,  FieldN, 2019 as CalendarYear , [2019] as CYValue
FROM yourTable WHERE [2019] is NOT NULL

Select allOpen in new window

This would  then give you the ability to use a query like

SELECT * FROM yourTable WHERE [CalendarYear] = 2018

Select allOpen in new window

Thank you Gustav (and all else who've contributed). I'm almost there.

Once I appreciated that the SQL string is stored like a string in a database and can be so modified after the combotable is changed, then it made sense where to put your code.

Now if I open the form which contains the query results, after I've changed the combobox, then that form has the new data.

However I'm not quite there. When I have the Holder form with the combobox (containing the form with the data as a subform) open and change the combo box, then the data doesn't change. I'm guessing that at the time you load the Holder form then it loads the SQL definition at that time, so even if I use Refresh or Requery, it is still using the old definition of the SQL language and hence doesn't have an effect. Is there a command to get the subform to effectively reload the SQL again - which would then include the updated SQL?

Many thanks

Form_NameOfSubForm.Requery

Select allOpen in new window

Unfortunately John, that is not working. I have a couple of other comboboxes on the form - which change criteria in part of the query, and they work. i.e. Requery refreshes the data on the table. But with this change to the actual SQL language, requery is not working. I can open the subform as a separate entity and it is updated, but Requery is not causing it to update on the already open form.

Probably you are not applying the new Recordsource to the subform...
If you used my implementation with the 2 queries it would work.

A little more colour might help solve this. Basically the data for the form comes from consecutive queries:

Query1 ->   Query2 (with SQL amends for year as per Gustav above) -> Query3 (with various criteria) -> FormData

When you load the form afresh, it obviously does the whole thing. However if I do Requery on the form, it seems to change Query3 (hence it adjusts for changes in criteria) but (I think) it doesn't recognise it needs to go back and rerun Query2 (with the changed SQL year).
Is there a way to trigger a Requery "up the chain" so to speak?

Since you only follow Gustav's implementation i guess is best to wait for his answer.

There are several issues which you must deal with.
1.  The fact that Query2 contains the SQL that gets you the correct field (2015-2019)
2.  What field(s) does the form display?  Does it display all of the fields (2015-2019) or is it only supposed to display the value for the field you have selected in your combo.

To address Item #1, you must understand that you will not be able to change the SQL string of the query if that query is already in use.  So, to resolve this, you are going to have to do something like the following in the afterupdate event of you combo:

Private Sub yourCombo_AfterUpdate

    docmd.echo false
    me.recordset = ""
    currentdb.querydefs("query2").SQL = "place your modified SQL string here"

    'you may have to call the after update events of your other combo boxes in here, to get the correct filter for t
    'selected year
     me.recordset = "query3"
    docmd.echo true

End Sub

Select allOpen in new window

To resolve item #2, you may have to change the control source value for the control that is supposed to display the calendar year, if you are only displaying the single year in the form.  But an easier way to handle this would be to alias the field name in "query2", something like:

SELECT Field1, Field2, ..., FieldN, [2018] as CalendarYear FROM yourTable

Then, simply give that control in you form a ControlSource of CalendarYear.

An old trick that might work is to reassign the RecordSource to itself:

Me.RecordSource = Me.RecordSource

Select allOpen in new window

Thank you all for all the helpful suggestions today. I've solved it.

Gustav unfortunately the old trick didn't work.

But I managed to retrigger the whole query chain by just using the DoCmd.BrowseTo acForm  command to take me to the same form and retrigger the full query chain.

Thanks again all.

Thanks for helping

I agree with Dale and so would other experts including the two who were very helpful to load your gun with bullets.  You have created a spreadsheet but you are using Access to host it.  This will be nothing but a problem for you going forward especially since you are not familiar with coding.  As you can see, everything you need to do requires code.  Next year when you want to add a new year, you will also need to change the table and  possibly forms and reports and code as well.  No expert would ever design the application this way.  

In a properly normalized schema, no column name contains data.   Think about what you've done.  Would you name columns, Suzy, Sam, and Joe if your first employee had three children?  Naming them Child1, Child2, Child3 is almost as bad.  What if an employee had 4 children?  How about 8?  Not common but certainly possible.  This is never a problem when you design your tables correctly because you don't expand horizontally.  Whenever you have more than one of something (years in your case), you have many and that means  multiple rows, usually in a second table.   To add more data to a table, you add rows, NOT columns.  Adding rows to a table has NO impact on ANY object design.  Your forms, reports, queries, macros, code simply just work.  You provide a parameter for the query that specifies which year you want and that's what you get back.  You can also easily run crosstabs which will show you multiple years at once.

We would be happy to help you normalize the schema and show you how to work with it that way.  It would be a much more productive use of all our time and you would be on your way to becoming an expert.

Thanks Dale and Pat re table design. I agree. In retrospect what you say makes sense and I've learnt for the future. However - it's not 1, but about 8 tables set up that way, and I've coded a whole bunch of import routines which bring in the data from other sources. To change now is not trivial and I need to get on with other things. The workaround provided above will work till 2023 as I've built out till then - so I've given myself another few years for going back to table redesign!

you're welcome.  Good luck.  Maybe you wont' have to change anything until then.