By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,147 Members | 744 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,147 IT Pros & Developers. It's quick & easy.

Referring to a Calculated Field in a Recordset

P: n/a
Greetings,

in A2K VBA, I set the following recordset:
Set rsGPValue = dbs.OpenRecordset("SELECT *,
DateSerial(Year([ValueDate]),Month([ValueDate])+4,0) FROM tblGPValue AS
OurDate, dbOpenSnapshot)

and then I try to use this calculated field:

If rsGPValue.Fields("OurDate") > {Forms]![frmReport]![RepDate].Value
Then [...more code to which the compiler never gets]

I get the "item not found in this collection" error. I verified that
the problem is with this created field (with a temp variable). A query
with this calculated field creates it just fine, and I can use it. Do I
refer to a calculated field in a recordset differently from a "natural"
field?

Thanks,
Yisroel

Jun 5 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
* ey*******@iname.com:
Greetings,

in A2K VBA, I set the following recordset:
Set rsGPValue = dbs.OpenRecordset("SELECT *,
DateSerial(Year([ValueDate]),Month([ValueDate])+4,0) FROM tblGPValue AS
OurDate, dbOpenSnapshot)

and then I try to use this calculated field:

If rsGPValue.Fields("OurDate") > {Forms]![frmReport]![RepDate].Value
Then [...more code to which the compiler never gets]

I get the "item not found in this collection" error. I verified that
the problem is with this created field (with a temp variable). A query
with this calculated field creates it just fine, and I can use it. Do I
refer to a calculated field in a recordset differently from a "natural"
field?

Thanks,
Yisroel


If OurDate is supposed to be an alias for the calculated field, I think
you have the "AS" in the wrong place.

Set rsGPValue = dbs.OpenRecordset("SELECT *,
DateSerial(Year([ValueDate]),Month([ValueDate])+4,0) AS
OurDate FROM tblGPValue" , dbOpenSnapshot)

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Jun 5 '06 #2

P: n/a
Randy Harris wrote:
* ey*******@iname.com:
Greetings,

in A2K VBA, I set the following recordset:
Set rsGPValue = dbs.OpenRecordset("SELECT *,
DateSerial(Year([ValueDate]),Month([ValueDate])+4,0) FROM tblGPValue AS
OurDate, dbOpenSnapshot)

and then I try to use this calculated field:

If rsGPValue.Fields("OurDate") > {Forms]![frmReport]![RepDate].Value
Then [...more code to which the compiler never gets]

I get the "item not found in this collection" error. I verified that
the problem is with this created field (with a temp variable). A query
with this calculated field creates it just fine, and I can use it. Do I
refer to a calculated field in a recordset differently from a "natural"
field?

Thanks,
Yisroel


If OurDate is supposed to be an alias for the calculated field, I think
you have the "AS" in the wrong place.

Set rsGPValue = dbs.OpenRecordset("SELECT *,
DateSerial(Year([ValueDate]),Month([ValueDate])+4,0) AS
OurDate FROM tblGPValue" , dbOpenSnapshot)


Duh :-) That was it, and the compiler didn't complain of SQL syntax. I
really shouldn't code late at night...

Many thanks, Randy!

Jun 5 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.