Add a field to a recordset

[stickers]

Hi all,

This may sound a bit daft, but I want to add a field to a recordset, with no values in it. My code loops through each record and will stick a value in the blank field. I don't want to make a table or anything like that, because I don't need the values except for using them in one subsequent calculation.

I'm using Access 2k and all my code is using ADO - so I get a bit confused by DAO...

Can anyone help?

Thanks

Sarah

 

[AWithers]

Have not tried it but when you declare your recordset cant you define the sql as something like

SELECT Yourfield1, "" AS NewField
FROM yourtable
then you can use the field 'newfield' to hold data in

Andy

 

[SgtJarrow]

The why use another field??? Just define a variable and use the variable instead.

****************************
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[stickers]

Thanks both for the replies. I'll give Andy's suggestion a shot today. mstrmage - the variable would be fine, but for the next step I need all the values produced as the code loops through. I need to add them together and find the Net Present Value of them.... is there a way of doing this without setting up a new field?

 

[SgtJarrow]

I would say yes, but can you provide some omre information? Can you give some examples of the data and what you are tryign to accomplish. I would venture to say you are making too much work for yourself and can easily tackle this problem with a variable or two.

****************************
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[stickers]

Great - thanks! I haven't been using Access for even a year yet, and before that I'd never done any programming, so I'm grateful for any thoughts on my work. It's entirely possible that you'll tell me I'm mad trying to do this!!!

Big picture is that the database is used to store and assemble capital and through-life costs of items of furniture used in building projects. This is traditionally done on spreadsheets, but this causes problems with auditability and data storage and retrieval. It is also a problem because the capital and through-life costs are assembled by different departments, who need data presented differently.

OK. The outputs are capital costs, which is straightforward, and through-life costs, which is a row of figures corresponding to spend in subsequent years. I've achieved this through crosstab queries.

To examine affordability of choices of furniture for a particular project, what I need to do is produce a 'deposit' figure, i.e. an amount of money that is put into a bank account to meet all the future expenditure. In Excel this is achieved through a goal-seek function. You have the row of expenditure figures (inflated each year), a closing and opening balance, and the deposit. The Excel model goal-seeks to make the closing balance always be positive at the lowest possible deposit.

So (eventually) to replicate this I've created a recordset which contains the expenditure. I loop through it starting with using £100 (or something suitable) as deposit, and use variables to get the opening and closing balances. If the closing balance is ever less than zero, the code exits the loop. Then I add £10 to the deposit and start again. So basically once the recordset reaches EOF you know that the deposit is right.

If you're still with me (and haven't got bored of my rambling), I need one more thing from the code: all the deposit figures (it gets inflated each year). I then need to add them up and discount them back to present-day value. I don't necessarily need this bit of code to chuck them out, but it might as well, while it's doing it. Hence the need to create a field in my recordset, to hold the information.

If you want to see my code let me know, but I thought I would not include it as this post is already huge!!!

Many thanks for any ideas you can give!

Sarah