Tags: access, database, de-referenced, field, fields, forexample, microsoft, mysql, named, oracle, reference, sequentially, sql, variable, variables

Using Variables to Reference a Field Name

On Database » Microsoft Access

2,551 words with 2 Comments; publish: Sat, 07 Jun 2008 23:03:00 GMT; (25093.75, « »)

Hello,

I'm trying to use a de-referenced variable to access a field name. For

example, if I have several fields named sequentially F1, F2, F3, F4, etc...

and I want to programmatically construct the field name to reference these

fields with a variable for the numeric part of the field name in a recordset,

how would I accomplish this?

Looking for something like:

NewVar = Recset!F[i]

Where "Recset" is a recordset to the table, and i is a loop variable that

counts from 1 to some higher value. I seem to remember that there is some

way to construct field names dynamically like this. Any help would be

appreciated.

Thanks, -BOBJ

All Comments

Leave a comment...

  • 2 Comments
    • "=?Utf-8?B?Qk9CSg==?=" <BOBJ.ms-access.questionfor.info.discussions.microsoft.com> wrote in

      news:CD935293-BB1B-40A8-AA5B-F2DBFBC3F647.ms-access.questionfor.info.microsoft.com:

      > I'm trying to use a de-referenced variable to access a field name.

      > For example, if I have several fields named sequentially F1, F2, F3,

      > F4, etc...

      ... then you have a big Design Problem...

      > and I want to programmatically construct the field name to

      > reference these fields with a variable for the numeric part of the

      > field name in a recordset, how would I accomplish this?

      > Looking for something like:

      > NewVar = Recset!F[i]

      NewVar = Recset.Fields("F" & format(i,"0"))

      ... but seriously, I would look at either reconstructing this thing into

      a database or using a spreadsheet app.

      Best wishes

      Tim F

      #1; Sat, 07 Jun 2008 23:04:00 GMT
    • Tim Ferguson wrote:

      > "=?Utf-8?B?Qk9CSg==?=" <BOBJ.ms-access.questionfor.info.discussions.microsoft.com> wrote in

      > news:CD935293-BB1B-40A8-AA5B-F2DBFBC3F647.ms-access.questionfor.info.microsoft.com:

      >

      > ... then you have a big Design Problem...

      I agree, it looks that way. What do these fields mean?

      [...]

      > ... but seriously, I would look at either reconstructing this thing into

      > a database or using a spreadsheet app.

      > Best wishes

      > Tim F

      Or both. You might consider using Excel spreadsheets to do your

      calculations (are you inverting matrices, or something, that requires

      array structures?), and linking to part of the Excel file as an Access

      (linked) Table that makes the results of the calculations available to

      your Queries, Reports, &c.

      -- Vincent Johns <vjohns.ms-access.questionfor.info.alumni.caltech.edu>

      Please feel free to quote anything I say here.

      #2; Sat, 07 Jun 2008 23:05:00 GMT