Savelie said:
Hi,
I need to retrieve only data where field name (not data) is like current
month. I want to do this, but I do not know the syntax:
SELECT Fields FROM table WHERE Fields LIKE CurrentMonth()
for instance if current month is December I want to retrieve Fields named
"Dec*" and data in that column
Any help is valuable
Almost always, having Fields for each month is "unnormalized", a violation
of good relational database design principles, and should be corrected
rather than encouraged by workarounds.
That is, sooner or later, perhaps when you find you need to select data only
for the current month as you do. Selecting current month's data would be
trivially easy if there were a Field identifying the month, or any date in
the month, so that you could use a Criteria of Month(Date()), and retrieve
the value.
That said, you can construct the SQL in VBA code... assuming your table is
tblMoFlds
Dim strSQL as String
strSQL = "SELECT " & Format(Date(), "mmm") & "FROM tblMoFlds"
But, unless you provide Criteria to choose Records, that will simply
retrieve the single Field, but for _every_ Record in tblMoFlds -- unlikely
to be very useful. And, assuming you are going to use it to populate a Form
or a Report, you'll have to have additional code to reset the RecordSource,
the Caption of the Control identifying data, and the ControlSource of the
Control displaying the data, too.
Just to make certain I wasn't misleading you, I did a sample... and it was a
pain to implement displaying the data in a Form, selecting only current
month for the current year, and displaying also CustomerNo and YearNo
fields.
Consider clarifying what you have and what you are trying to accomplish
(instead of how you'd thought to accomplish it) and perhaps someone can give
you useful suggestions, instead of just leading you farther down the wrong
path.
Larry Linson
Microsoft Access MVP
