Dynamically Referencing fields in a MS Access queue

  • Thread starter Thread starter Joe Ritter
  • Start date Start date
J

Joe Ritter

I'm dealing with a poorly designed MS Access database. Fieldname include the
month/year of the data. I need to know current months values and that needs
to change from month to month. Is there a way to dynamically reference the
field name within a queue? I've got the code to build the name itself, but I
can't figure out how to tell Access that my results are the name of the field
to be pulled.
 
Only by using VBA to build the query string on the fly and then assigning that
as the record source of the form or report or assigning it as the SQL of a query.

Dim strSQL as String
Dim strFieldName as String
strFieldName = "[" & Format(Date(),"YYMM") & "]"

StrSQL = "SELECT " & strFieldName & ", [NextField], [FieldTwo], [Fielda]" & _
" FROM [Name of some Table]" & _
" WHERE " & strFieldName & " = ""SomeValue"" "

Now with the valid SQL statement assign that as needed to a form, report,
combobox, query, etc.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Back
Top