Referring to a Calculated Field in a Recordset

[ey.markov]

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

[Randy Harris]

* 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.

[ey.markov]

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!