Solved

Recordset using a variable

Posted on 2011-03-02
3
318 Views
Last Modified: 2013-12-25
I have a recordset based off a select query that work fine.  Note:  It will only ever return one record.
When I try to get the data from the record set it works fine if I explicitly tell it the column name as below:
oldDateResult = rs![The Column Name I Want]
But I have a variable which contains the column name which I need to use,
colName = "[The Column Name I want]"
oldDateResult = rs!colName
This doesn't work, says it can't find the column.  I hope I explained it good enough, just need to know how to reference the column name by the use of a variable.

Thanks,
0
Comment
Question by:bvanee1
3 Comments
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 35021560
I don't believe you can dynamically reference a recordset field like that, but you could reference it by index. I.e. you can find out the column with a loop and then reference the index. Just make sure the column name you want exists otherwise you'll have an endless loop!

x = 0
Do Until rst(x).Name = "[The Column Name I want]"
     x = x + 1
Loop
oldDateResult = rs(x)

HTH,

MV
0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 35021692
Try:

rs.Fields(TheColumnNameIWant)
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 35021775


rs(YourStringVariableName)

eg.

Dim sMyFldName, x

sMyFldName="ThatField"
x=rs(sMyFldName)

mx

mx
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question