Varibale to Reference Table Column

[MXS]

I hope someone can help because I submitted a request to Microsoft and they
redirected me nowhere!
How do I use a variable to reference a column in MS Access? I am trying to
write code and want to assign the column name via a variable so I can set the
column to use based on some criteria inside of having to code for each column.

 

[Rick Brandt]

I hope someone can help because I submitted a request to Microsoft
and they redirected me nowhere!
How do I use a variable to reference a column in MS Access? I am
trying to write code and want to assign the column name via a
variable so I can set the column to use based on some criteria inside
of having to code for each column.

A "column"? Do you mean a field in a Recordset or in a form?

In a Recordset...
RecordsetName.Fields(VariableName)

In a Form...
Me.Fields(VariableName)

....or...
Forms!FormName.Fields(VariableName)

 

[Douglas J. Steele]

Reference a column where?

I suspect you have a recordset, and you want to refer to a field in it
programmatically.

That would be rst.Fields(strField), where rst is the instantiated recordset,
and strField contains the name of the field.

Same basic approach if you're using DAO to refer to tables and fields: you'd
use tdf.Fields(strField), where tdf is the instantiated tabledef object, and
strField contains the name of the field.

 

[MXS]

I mean a field in a table (recordset). I am not using DAO. I am creating a
do loop and want to cycle through the fields (named 1-31, for days in the
month). so the variable would increment a variable by and I want to use that
value to reference the table field. rstname![variablename] or
rstname!variablename or what ever will work.

Thanks for you time, expertise and reply.

 

[Douglas J. Steele]

Sounds as though your table isn't properly normalized and that you have a
repeating group.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I mean a field in a table (recordset). I am not using DAO. I am creating
a
do loop and want to cycle through the fields (named 1-31, for days in the
month). so the variable would increment a variable by and I want to use
that
value to reference the table field. rstname![variablename] or
rstname!variablename or what ever will work.

Thanks for you time, expertise and reply.

--
MXS

A "column"? Do you mean a field in a Recordset or in a form?

In a Recordset...
RecordsetName.Fields(VariableName)

In a Form...
Me.Fields(VariableName)

....or...
Forms!FormName.Fields(VariableName)

 

[John Spencer]

In a recordset (named rstAny) you could refer to the fields by their ordinal
position or by the name of the field

rstAny.Fields(0) << first field in rstAny
rstAny.Fields("1") << Field named "1"

Extending the second option:
rstAny.Field(StringVariableContainingFieldName)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I mean a field in a table (recordset). I am not using DAO. I am creating a
do loop and want to cycle through the fields (named 1-31, for days in the
month). so the variable would increment a variable by and I want to use that
value to reference the table field. rstname![variablename] or
rstname!variablename or what ever will work.

Thanks for you time, expertise and reply.

Two comments:

1. Please post your code.
2. If you have fields named 1 through 31, I HOPE you're working to migrate the
data into a properly normalized structure, because this table design is WRONG.

You can use the Fields() collection of a Recordset; as noted elsethread you
have two choices for how to reference the field. A String (variable or
literal) will select the fieldname; a Number will return that ordinal position
in the recordset. For example, if your recordset is based on a table or query
with fields like

ID; [Year]; [Month]; [1]; [2]

you can refer to rs("1") to get the field named [1], or you can use rs(3) to
get the same field (it's zero based, rs(0) is the ID).

My example is a horrible example for two reasons - the repeating fields are
Committing Spreadsheet, and I used the reserved words Year and Month as
fieldnames. So don't DO that... <g>

 

[Tony Toews [MVP]]

Sounds as though your table isn't properly normalized and that you have a
repeating group.

You know there are times when it is appropriate to normalize this kind
of data and there are times when it's not. And this just might be one
of those times.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I mean a field in a table (recordset). I am not using DAO. I am creating a
do loop and want to cycle through the fields (named 1-31, for days in the
month). so the variable would increment a variable by and I want to use that
value to reference the table field. rstname![variablename] or
rstname!variablename or what ever will work.

Try rs.Controls("FieldName" & i) where i is the day (increment).

I also created a blog entry about this with respect to multiple
controls on the form.
http://msmvps.com/blogs/access/archive/2008/09/16/referencing-one-of-three-identical-controls.aspx

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Douglas J. Steele]

You know there are times when it is appropriate to normalize this kind
of data and there are times when it's not. And this just might be one
of those times.

I agree there's a time and place for denormalization, but having a field for
each day of the month? I'd have to be convinced.

One exception would be if you wanted to have 31 text boxes in a calendar
shape on a bound form and have the form be updatable. However, the fact that
he/she is trying to cycle through the fields in code makes me suspect it's
unbound.