hi,
i'm trying to copy a recordset into a variable. i can copy it to an excel cell with the
command, but as soon as i try to copy the value to a variable using:Range("D5").CopyFromRecordset rst
it returns a null value...Set temp = Recordset or temp = Recordset
i need to perform some calculations using another value i've grabbed from a table so what would be the best way to get my value into a variable?
the value returned is a single digit btw
thanks
Jonathan
Last edited by Jollyfrog; 10-21-2010 at 09:07 AM.
[removed - nonsense I'm afraid...]
Last edited by DonkeyOte; 10-21-2010 at 07:13 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Jonathan, earlier post was nonsense, apologies!
Dim vTemp As Variant ... If rst.EOF = False Then vTemp = Application.Transpose(rst.GetRows)
Last edited by DonkeyOte; 10-21-2010 at 07:16 AM. Reason: removed note re: Transpose as it's always relevant
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
hmm i've tried this and it seems to think the EOF is true.. i copy the recordset into a cell just to see whats going on... and that returns the correct value, 27...
vtemp doesn't return any value as nothing is parsed into it
thanks
rst already is a variable, so I don't really see what it is you want to do?
i wan't to perform an arithmetic operation on it.
like that only a bit more complicated...temp = rst * 2 Cells(5, 5).Value = temp
currently code like that returns type mismatch error
To clarify what i want to do:
i'm ok with getting balues from tables (thanks to some helplookup a number of values in a table 1 and add them record this value as A lookup a reference in table 2 record this as B get records from table 3 corresponding to value b record this as C multiply A and C output this to a cell)
however recording values returned from the tables is presenting a difficulty.
thanks
Jonathan
Wouldn't it be easier to do all that in the query (or queries)?
You can't simply multiply a recordset by 5 as a recordset can contain many records and many fields per record. If you only have one field and one record, then you need to refer to that field specifically:
for example.rst!Field_name * 5
i wanted to parse the record to a variable (integer) so i could then perform mathematical operations upon it. I also need to use the result of a query to form the paremeters for the next query, i thought the best was to do this would be to use a variable set from a recordset.
I'm not really sure how to the whole process as a single query, i assumed combining 3 separate queries was better.
however
when i use the code
range g5 returns the correct value (27)rst.Open "Select SUM([alpha] + [bravo] + [charlie] + [delta] + [ecco] + [golf]) as subs FROM OperatorDetail WHERE [Operator Name] = 'WARNER' AND Country = 'Germany'", conn Range("g5").CopyFromRecordset rst temp = rst!subs
but temp does not accept a value
Put the temp line before the CopyFromRecordset line.
wait whaat!.. that works hmm.. well thankyou very much :-)
does copyfromrecordset delete the record then?
thanks again
No, it doesn't delete it, but it does move to the end of the recordset, so there is no current record for you to read.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks