Access DAO Query

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

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

www.fmsinc.com/consulting

"Bill Andersen" Bill wrote in message
...
In Access 2000 DAO, what is the syntax to refer to a field in an open
query?

"Bill Andersen" Bill wrote in message
...
In Access 2000 DAO, what is the syntax to refer to a field in an open
query?

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

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!
--
Chaim


"Bill Andersen" wrote:

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

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

"[MVP] S.Clark" wrote:

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

www.fmsinc.com/consulting

"Bill Andersen" Bill wrote in message
...
In Access 2000 DAO, what is the syntax to refer to a field in an open
query?

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

"Margaret Bartley" wrote:


"Bill Andersen" Bill wrote in message
...
In Access 2000 DAO, what is the syntax to refer to a field in an open
query?

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