MS Access Vb How to build a text string to reference a dynamic column in and Vb SQL query and then execute that text string using the Eval function

Question

In the following example where POOPTrst is a a DAO recordset (systems object) referencing the Vb SQL query or statement. this code returns "Run-time error 2482" and/or "Microsoft Access cannot find the name 'POOPTrst' you entered in the expression" (note: the POOPTVal variable holds a date).

POOPTWkDmd = Eval("POOPTrst" & "!" & POOPTVal)

In the following example where POOPTrst is a a DAO recordset referencing the Vb SQL query. this code returns "Run-time error 3256" and/or "Item not found in this collection" (note: with or without parenthesis around the variable POOPTVal)

POOPTWkDmd(POOPTCounterInt) = POOPTrst!Eval(POOPTVal)

If I remove the Eval function and the POOPT date variable and type literal characters into the code I get the proper/expected return value and/or response (note: the problem is that 1/5/2009 is a dynamic value that is calculated at run time

POOPTWkDmd(POOPTCounterInt) = POOPTrst![1/5/2009]

Then it is not MySql and it is not VB.NET. The tags include descriptions and instructions when to use and when not too — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jan 23, 2016 at 21:47
My error... I'm new to stackoverflow and I think these were the default tags — California1961
– California1961, Commented Jan 23, 2016 at 21:50

Community · Accepted Answer · 2017-05-23 12:15:16Z

2

Use this syntax: rs("Fieldname") instead of rs!Fieldname

POOPTVal = "1/5/2009"
POOPTWkDmd = POOPTrst(POOPTVal)

for more explanation see this answer: https://stackoverflow.com/a/34969410/3820271

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Jan 23, 2016 at 22:14

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HansUp · Accepted Answer · 2016-01-23 22:14:52Z

2

One issue is that Eval() doesn't know anything about VBA variables or objects such as recordsets. If you want to use those, build a string containing their values and give Eval() that string.

However, I'm not sure Eval() is what you should use here. It seems you want to reference the value of a field in your POOPTrst recordset, with a variable to hold that field name. If that is correct, use the variable with the recordset's Fields collection: POOPTrst.Fields(POOPTVal).Value

answered Jan 23, 2016 at 22:14

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Collectives™ on Stack Overflow

MS Access Vb How to build a text string to reference a dynamic column in and Vb SQL query and then execute that text string using the Eval function

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related