Frank The Novice said:
Hello All,
I have a table that includes several fields of arrival times. They are
named AT1, AT2, AT3, etc...
I have another field named Sector that holds a number from 1 - 10 to
correspond with the current Arrival Time.
I need to grab the last arrival time and time to it to get an ending time
for the day. What I've been trying to do is concatenate "[AT" & [Sector]
&
"]". This returns the correct field name, but I can't get it to return
the
time stored in that field.
Can anyone offer a solution?
<lecture>
It sounds like your table design is faulty. Situations where you have
multiple fields in a table containing the same type of information, such
that you end up numbering them (for example) AT1, AT2, AT3 ..., make it very
hard to query those fields. Some people refer to this as "committing
spreadsheet" -- database tables aren't spreadsheets, and shouldn't be
constructed as if they were.
Instead, it would be better to store the repeating information as separate
records in a related table, as for example,
SeqNo ArrivalTime
----------- ----------------
1 9:00 AM
2 10:00 AM
3 11:00 AM
4 12:00 AM
5 1:00 PM
6 2:00 PM
7 3:00 PM
8 4:00 PM
9 5:00 PM
10 6:00 PM
Of course, there would also be one or more fields to relate each of these
records to the main record in the original table.
A vertical structure like that is much easier to query, and no code is
needed to do so, because everything you need to identiify the value you want
is the relevant record keys. If you have any control over the design of
your tables, you would do well to restucture them to remove redundant and
repeating information. This process is called "normalization", and you'll
find lots of discussions of it on the web.
</lecture>
Assuming you can't restructure your tables at this time, the answer to your
specific question about using the calculated name of the field you want is:
yes, you can do it, but the method depends on the context.
Let's suppose you have figured out the name of the field you want to extract
and stored it in a string variable called "strFieldWanted". Now you want to
get the value of that field. If you have a recordset named "rst" open on
the table in question, and positioned to the record you're interested in,
then you can use a syntax like this:
vArrivalTime = rst.Fields(strFieldWanted)
The name of the field is used as an index into the recordset's Fields
collection.
If you don't have a recordset open (and don't want to open one), you can use
DLookup:
vArrivalTime = DLookup(strFieldName, "YourTableOrQuery", "ID =
whatever")
You need to modify that example to provide the name of the table or query
and the criteria necessary to specify the record you want to extract from.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
