Getting the contents of a field!!

[John M]

I'm lost on somethingthat I feel ought to be simple - getting the contents
of a field!

The database relates to school students and their subjects. The current
record has as its source a query which combines a number of tables, each for
a specific subject. These have a number of common fields such as 'target'.
I want to fnd say the total or average of target for those different
subjects. I have all the subject names sitting in an array.
Subject(1)-"En" etc. Just to see I've got it can do
Something = [En].[Target]
MsgBox Something produces 6
but
Something = "[" & Subject(1) & "].[Target]" produces "[En].[Target]" not
the contents of field!
I've tried brackets all over the place an ' not "

I feel the solution is so obvious but I can't see it.

[Brian]

"John M" <jo**@jmawer.demon.co.uk> wrote in message
news:ck*******************@news.demon.co.uk...

I'm lost on somethingthat I feel ought to be simple - getting the contents
of a field!

The database relates to school students and their subjects. The current
record has as its source a query which combines a number of tables, each for a specific subject. These have a number of common fields such as 'target'. I want to fnd say the total or average of target for those different
subjects. I have all the subject names sitting in an array.
Subject(1)-"En" etc. Just to see I've got it can do
Something = [En].[Target]
MsgBox Something produces 6
but
Something = "[" & Subject(1) & "].[Target]" produces "[En].[Target]" not
the contents of field!
I've tried brackets all over the place an ' not "

I feel the solution is so obvious but I can't see it.

Something = Me("[" & subject & ".Target]")

[Brian]

"Brian" <bc**@IHATESPAMclara.co.uk> wrote in message
news:10***************@echo.uk.clara.net...

"John M" <jo**@jmawer.demon.co.uk> wrote in message
news:ck*******************@news.demon.co.uk...

I'm lost on somethingthat I feel ought to be simple - getting the contents of a field!

The database relates to school students and their subjects. The current
record has as its source a query which combines a number of tables, each

for

a specific subject. These have a number of common fields such as

'target'.

I want to fnd say the total or average of target for those different
subjects. I have all the subject names sitting in an array.
Subject(1)-"En" etc. Just to see I've got it can do
Something = [En].[Target]
MsgBox Something produces 6
but
Something = "[" & Subject(1) & "].[Target]" produces "[En].[Target]" not the contents of field!
I've tried brackets all over the place an ' not "

I feel the solution is so obvious but I can't see it.

Something = Me("[" & subject & ".Target]")

Sorry, forgot the subscript, that should of course be:

Something = Me("[" & subject(1) & ".Target]")

[MacDermott]

You might try
Something = Eval("[" & Subject(1) & "].[Target]")

"John M" <jo**@jmawer.demon.co.uk> wrote in message
news:ck*******************@news.demon.co.uk...

I'm lost on somethingthat I feel ought to be simple - getting the contents
of a field!

The database relates to school students and their subjects. The current
record has as its source a query which combines a number of tables, each for a specific subject. These have a number of common fields such as 'target'. I want to fnd say the total or average of target for those different
subjects. I have all the subject names sitting in an array.
Subject(1)-"En" etc. Just to see I've got it can do
Something = [En].[Target]
MsgBox Something produces 6
but
Something = "[" & Subject(1) & "].[Target]" produces "[En].[Target]" not
the contents of field!
I've tried brackets all over the place an ' not "

I feel the solution is so obvious but I can't see it.