Select SQL Query using input from form fields Acce

Question

I have a form with two combo boxes and a button that runs a select query with the code below. Currently the issue is it doesn't take the input of the combo boxes as the field input. Is this something that can be solved in SQL or would I have to build a form using VBA?

 SELECT Field=[Forms]![TestInteractForm]![Combo18], Field=[Forms]![TestInteractForm]![Combo20]
FROM TestInteract;

Answer 1

Although it may not be apparent, your goal is basically the same as using a query parameter in the SELECT clause to identify a field.

Unfortunately, Access does not support that feature. Your combo box value, which is a field name, will be recognized as a valid text string, but there is no way to inform Access that string should be interpreted as a field name.

The closest you can get would be to use a Switch statement which maps each field name string to the corresponding field value. So, if TestInteract and Combo18 both include 4 fields (fld1, fld2, fld3, and fld4) something like this could actually work ...

SELECT
    Switch(
        [Forms]![TestInteractForm]![Combo18]='fld1', [fld1],
        [Forms]![TestInteractForm]![Combo18]='fld2', [fld2],
        [Forms]![TestInteractForm]![Combo18]='fld3', [fld3],
        [Forms]![TestInteractForm]![Combo18]='fld4', [fld4]
    ) AS first_column
FROM TestInteract;

However, I'm not recommending you adopt that method. I think it could be simpler to generate the SELECT statement in your command button's click event.

Dim strSelect As String
strSelect = "SELECT [" & Me.Combo18.Value & "]" & vbCrLf & _
    "FROM TestInteract;"

And then use strSelect where you need it ... as a form's RecordSource, as the data source for a recordset, or as the .SQL property of a saved query.