I had hoped that this was a help forum, not a vehicle for a self-describer immoderate moderator to vent his spleen over an irrelevance without attempting to understand.
Actually, it IS a help forum. I was, in fact, offering help - if you looked far enough. My initial answer, given in post #4, contained two parts. One was a criticism of the approach. Get past that because the other part of post #4's answer was the immediate and direct solution to your problem.
You are attempting to use a type of notation called "bang" notation after a common nickname for the "!" character. You know that we use names that end in DOT COM (.COM) when talking about web sites. I.e. we pronounce the DOT. Well, when talking in shorthand like we usually do, you talk about DOT or BANG as a way to specify the punctuation in the string we are reading.
Access VBA has DOT notation, too. It is the syntax indicator for examining properties of objects... such objects as your recordset, which is a particular type of object. Using DOT notation, you can look at properties such as the .Bookmark or the .EOF or .BOF indicators.
One of the properties of an open recordset object is the .FIELDS property, which returns a COLLECTION of individual FIELD objects, one for each field in the recordset. If you have five fields in a record, you will have five FIELD objects as members of the recordset's FIELDS collection. Each of the FIELD objects has a text name that is unique within that collection.
Usind DOT notation, you can select one of the FIELD objects in the FIELDS collection by using its text name, as in
rdsR.Fields("R01"). BANG notation looks at members of collections, but is picky about the syntax because it a RUN-TIME operator. The correct syntax with BANG notation would be
rdsR!R01. There is a third notation that would also work...
rdsR("fieldname").
There: THREE ways to do what you want.
1.
rdsR.Fields( "fieldname" ) - which returns the value of the field, and the name CAN be built by concatenation.
2.
rdsR!fieldname - which returns the value of the field, but you cannot build the name by concatenation via run-time evaluation.
3.
rdsR( "fieldname" ) - which returns the value of the field and the name CAN be built by concatenation. Option 3 is actually option 1 but taking into account those properties that are defaults.
More precisely, using DOT notation, your actual reference is
rdsR.Fields( "fieldname" ).Value - but by omitting the explicit references to the default properties for each object, you omit
.Fields and
.Value, leaving you with option 3. Note that this is a COMPILE-TIME reference, for which the only thing that matters at run-time is the fieldname value, which can be passed in via string constant or via a string variable containing the fieldname. BANG notation DOES NOT SUPPORT concatenating the name in that context.
Summary: GIVE UP on the BANG notation for this process. You MUST use a method that allows you to build the name via concatenation. BANG notation is not going to help you here.
