StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The short answer to your question is that you can't have dynamic field names in the Access query builder. Ideally, you should modify your data so that you have one date field. That way you can specify a date range for that field in the query criteria that doesn't require the gymnastics below.</p> <p>If that's not possible, though, the long answer is that you'll need to use VBA code to construct your query. The code below should create a new query in your database (cleverly called "YourQuery") that will contain the fields you need.</p> <p>Since I don't know what your table is called, it'll be "table" in this example. You should change it to whatever your table is if you use this code.</p> <p>You'll need to install this code in a VBA module in your database, and then devise some means of calling the function to produce the new query. For example, you could create a macro using the "RunCode" action that uses "DynamicQuery()" as the procedure to run. Note that the function call will fail if the query for this month already exists so delete that manually if you need to run it again for some reason (you could also add additional code to function to accomplish this).</p> <p>Again, just because you can do something doesn't mean you should. You really should redesign your table so you don't have to resort to workarounds like this.</p> <pre><code>Public Function DynamicQuery() Dim strSQL As String dim datFieldDate As Date Dim strYearMonth As String Dim strThisMonth As String Dim dbs As DAO.Database Dim qdf As DAO.QueryDef Dim i As Integer Set dbs = CurrentDb strThisMonth = Year(Date) & "_" & Month(Date) strSQL = "SELECT table." & strThisMonth For i = 1 to 12 datFieldDate = DateAdd("m",i,Date) strYearMonth = Year(datFieldDate) & "_" & Month(DatFieldDate) strSQL = strSQL & ", table." & strYearMonth Next i strSQL = strSQL & " FROM table;" Set qdf = dbs.CreateQueryDef("YourQuery_" & strThisMonth, strSQL) Set qdf = Nothing Set dbs = Nothing End Function </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload