Field names in recordset 1

[cjac]

Hi, does anyone know how to view what field names are available in a recordset, ie. I am trying to access the sysdate from the following query:
SQLstring = "select trunc(sysdate) from dual"
I then execute this SQL using an ado connection to an Oracle DB. What I want to do now is read the result into a public var, but when I try to reference the field with:
myvar = RS.fields!sysdate
but I get "the item cannot be found in the collection" when hovering over the code in question.
I think it's got to be down to the retrieval of sysdate from dual although SQL plus display the fieldname as (trunc)sysdate. Any ideas.

Cheers.

 

[zemp]

Try,

myvar = RS!sysdate

or

myvar = RS.fields("sysdate").value

zemp

 

[cjac]

thanks Zemp for the response but I've tries both methods and still the item is missing from the collection. Not sure what fieldname is being assigned!? Is there anyway or querying the recordset to see exactly what the fieldname is in the recordset?

 

[hmckillop]

you can try naming the field i.e.
"select trunc(sysdate) as myfield from dual"
and then accessing it using
rs("myfield")


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[hmckillop]

if you need to query the recordset you can see what it is by rs(0).name and this will tell you what is assigned, alternatively if you know it will always be the first field just use rs(0) to access the value



"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[cjac]

cheers hmckillop - the recordset field name was TRUNC(SYSDATE) although if you attempt to reference a field in that way VB assumes the content of anything within brackets is a variable. I've just renamed the field name within the SQL statement as you suggested.
Thanks for the help. Much appreciated.

 

[kainite]

To see what fields you have in a recordset just use
Dim rs as Recordset
Dim f As Field
open rs as normal
then e.g.
For Each f in RS.Fields
Debug.? f.Name
Next

Of course aliasing as in earlier replies removes ambiguity of calcualted fields so is obviously what you should have done oin the first place but above will help if ever you don't know what cols you have