Accessing Fields from a Recordset

[dakota81]

I'm running Access 97, and have a query that's set up like:

SELECT R1!ID, R1![Part Number], R1![Quantity], R1![BackOrder], R3![Type] FROM...

I'm doing a join of 3 tables and pulling out the information I need. Now, my question is, what is the syntax to access the fields of each tuple in the recordset? They all are unique names, so I tried rstRecordset![Part Number] for instance, but it gave me an Item Not Found in Collection error. Next I tried rstRecordset!R1![Part Number] but didn't do me much good either.

I got the information out by typing rstRecordset.Fields(1) and everything worked fine for me, but I'm wondering what the syntax is for accessing the fields by name.

 

[MarkSweetland]

Since I don't have Access handy at the moment, just a couple of thoughts to assist til I do...

If you could walk through the recordset with the rs.fields(1) syntax, could you not also use rs.Fields(1).Name to see the column name assigned by the SQL statement?

If you use an alias for the columns in your SQL statement, you could maybe set the column names:

SELECT ID=R1!ID, PartNumber=R1![Part Number], Qty=R1![Quantity], BackOrder=R1![BackOrder], Type=R3![Type] FROM...








Mark

 

[CCLINT]

rstRecordset("Part Number&quot [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[ddewhitt]

There are some "shorthand" ways to refer to a field.

rs.fields(0).value returns the contents of the field. So does rs(0).value. To use the field name then use the name delimitted with double quotes instead of the ordinal like rs("MyFieldName&quot.value