ACCESS FEED: [MS_AccessPros] Can I synthesize a field name?

Rabu, 28 Oktober 2015

[MS_AccessPros] Can I synthesize a field name?

I have a table 'Attendance' (derived from an Excel import) with fields
Surname, Forename,
followed by many other text fields all named as dates e.g. 28/10/15. The
table has about 300
surnames, and each person's name has a possible letter in each date field
e.g. "M"
(for Member) or "V" (for Visitor), indicating their presence at a meeting on
that date.

I display a subform covering the 10 dates in each year, showing attendance.
The parent form has
as its data source a table 'AllDates' with fields Year1, Date1,...Date10.
The table contains
the relevant dates for each of 8 years. I have 10 textboxes on the parent
form which show the
relevant dates for one year, derived from 'AllDates', acting as headers for
the fields in the
subform.

At present I can achieve my aim by creating separate queries for each year,
picking out the
relevant fields from 'Attendance'. I select the appropriate query by joining
"Q" with the year
from a text box 'Year1' on the parent form, making query names like 'Q2008'.

I would like to have just one query instead of ever-increasing numbers of
year-queries. To do
this I started by making a query 'DateSelect', giving all the names, with
columns Date1 to
Date10 showing the same 10 dates opposite each name, derived from
'AllDates'.

I now want to extract the corresponding characters from 'Attendance' held in
the columns for
those dates. So I made another Query that had a string expression
"[Attendance].[" &
[AllDates].[Date1] & "]" in field 'D1:', and similarly for the fields up to
'D10:'. This
resulted in strings like "[Attendance].[17/09/08]" in the listing, but not
the contents of
those fields. I thought that Eval function applied to the strings would
convert them into their
contents, but all I get when I run that query is "#Error" in those cells -
not very helpful.

I've searched on line for answers, but the two similar requests I found did
not receive proper
answers. Can anybody here say whether this is possible, and if so how?

Maybe I could synthesise the SQL to generate a query for one year, and
operate that somehow without having to save it every time?
--
Dave W