try this and let me know if it works for you

Query Criteria

Let me preface this by saying I don't know what you are doing or the reasoning behind the structure of your table.

Good practice is to not have repeating fields in your tables.  I can't think of an instance where I would justify having 12 fields, 1 for each month.  You might want to check out the Normalizing Data video as well a the Relationships videos.

Just thought  about this try CrossTab
Expert Level 17
Expert Level 18

Hi Kim, Access can't filter columns; it can only filter rows, which is how this type of database works.  To do what you need, you need to construct the SQL with some tricky VBA.  For instance:

Private Sub UserEntry_AfterUpdate()
    Dim m As String, sql As String
    
    ' Check if user entry is between 1 and 12.
    If UserEntry >= 1 And UserEntry <= 12 Then
    
        ' Create 3-letter month name based on user entry
        m = Format(DateSerial(2023, UserEntry, 1), "mmm")
        
        ' Construct SQL
        sql = "SELECT " & m & " FROM MyTable;"
        
        ' Use this SQL as the recordsource of the form that needs it. For instance:
        Me.RecordSource = sql
        Me.Requery
        
    Else
        MsgBox "Invalid entry."
    End If
End Sub

If the user enters the number in a field called UserEntry, the above code will convert that number into a 3-letter month name, which is then used as a field name for the query.  Access uses standard 3-letter month names: e.g., March is Mar, June is Jun, September is Sep, etc.  So your field names need to be exactly these.

What Scott alluded to in his post is correct: this is not a good way to design a table.  Queries in Access filter rows, not columns.  If you force it like I did above, you may get by once or twice, but will eventually run into problems.  Anything that needs to be filtered needs to be put in rows.  For instance:

MyMonth    MyValue
1          100
2          35
3          43
2          739
6          3
10         55
6          43

For a table like the above, it is a trivial task to filter out data for the month 2:  SELECT * FROM MyTable WHERE MyMonth = 2;  

Result:
MyMonth    MyValue
2          35
2          739

Thank you for you suggestion.
I ended up using VBA to solve the problem.

I am building the SQL and incoorporating the "month" value from the form into the SQL by concatenation.
Then i check if Query exist and delete it if this is the case.
and finally i save the new query with the data from the chosen month.

It works and i get the result i wanted - maybe not in the most effective/correct way.
Regarding the use of 12 fields it might not be the best solution - but now i can learn from it - but i decided it is to late to change for this project.

but never too late to make it better.