Access DAO Query

B

Bill Andersen

In Access 2000 DAO, what is the syntax to refer to a field in an open query?
 
M

[MVP] S.Clark

Trick question.

An opened query on screen is not a reference-able object, like say a form
is. Thus once it's opened, it's just there.

Explain what you're really trying to do, and we'll tell you how to do it.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting
 
M

Margaret Bartley

Bill Andersen said:
In Access 2000 DAO, what is the syntax to refer to a field in an open
query?
Click to expand...

I hope I'm not exposing my ignorance here, but I don't think you can get to
a field in an open query. What you want to do is create a recordset based
on that query, and then reference the field in that recordset.

You have to know either the name of the field, or which order it is.

If you know it is the third field, but not the name, you can do:

dim db as Database
dim rs as Recordset
dim strFieldName as String, iContents as Integer

set db=CurrentDB()
set rs=db.OpenrRecordset("My Query")

strFieldName=rs.Fields(3).Name


That gives you the field name. To reference that field:
iContents=rs(strFieldName)

If you already know the field name, "MyField", you can reference it :
iContents=rs!MyField
 
C

Chaim

Any of the following will do:

dim rst as DAO.Recordset

set rst = CurrentDB.OpenRecordset ("<name of a table or query">)
debug.print "Value of a field in rst: " & rst.<name of the field>
OR
debug.print "Value of a field in rst: " & rst.Fields ("<name of the field>")
OR
debug.print "Value of a field in rst: " & rst.Fields(<Ordinal referring to
the field, starting at 0>)

Good Luck!
 
B

Bill Andersen

Chaim showed me the way. I need to open the query as a recordset, then I can
refer to the field as "rst.fields("field name").

thanks for your attention.

Bill Andersen
 
B

Bill Andersen

Dear Ms. Bartley,

Chaim showed me the way. I need to open the query as a recordset, then I
can refer to the field as "rst.fields("field name"). You made the same
suggestion though in a slightly more complicated way.

Thanks for your attention.

Bill Andersen
 
Top