visual basic - retrieve values from table and perform calculation

Hi,

I trying to retrieve values from a table to calculate the 14days average 
value of a stock closing price. However, i encounter some problem as stated 
beside the code as follows:

Function DaysAvgs()
'Calculate the average value of a given value.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varBookmark As Variant
Dim numAve, numDaysAvg As Double
Dim intA, intB, lngCount As Integer

Set db = CurrentDb

'Open Table
Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable)

rst.MoveFirst

Do While Not rst.EOF
intA = 1
intB = 0
varBookmark = rst.Bookmark
numDaysAvg = 0
numAve = 0

For intA = 1 To 14
    numAve = rst.Fields!Close + numAve  { When run, can't find record }
    intB = intB + 1
    If Not rst.EOF Then
    rst.MoveNext
    Else
    intA = 15
    End If
Next intA

rst.Bookmark = varBookmark
numDaysAvg = numAve / intB
rst.Edit
rst.Fields!DaysAvg14 = numDaysAvg
rst.Update
rst.MoveNext
Loop

Set rst = Nothing
Set db = Nothing

End Function

Please help. Thank

Nelson Chou
0
Utf
6/27/2007 1:00:12 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1233 Views

Similar Articles

[PageSpeed] 21

Hi,

I have manage to locate the fault on the For and Next statement:

 For intA = 1 To 14
>     numAve = rst.Fields!Close + numAve  { When run, can't find record }
>     intB = intB + 1
>     If Not rst.EOF Then
>     rst.MoveNext
>     Else
>     intA = 15
>     End If
> Next intA

The problem encounter problem when there left only 13records in the table. 
But i thought i have this statement "If Not rst.EOF then rst.MoveNext Else 
intA = 15" to exit the loop. It can't work........even i rewrite it with Else 
Exit For (instead of intA = 15).

Any help.

Thank.

Nelson

"Nelson The Missing Lead" wrote:

> Hi,
> 
> I trying to retrieve values from a table to calculate the 14days average 
> value of a stock closing price. However, i encounter some problem as stated 
> beside the code as follows:
> 
> Function DaysAvgs()
> 'Calculate the average value of a given value.
> 
> Dim db As DAO.Database
> Dim rst As DAO.Recordset
> Dim varBookmark As Variant
> Dim numAve, numDaysAvg As Double
> Dim intA, intB, lngCount As Integer
> 
> Set db = CurrentDb
> 
> 'Open Table
> Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable)
> 
> rst.MoveFirst
> 
> Do While Not rst.EOF
> intA = 1
> intB = 0
> varBookmark = rst.Bookmark
> numDaysAvg = 0
> numAve = 0
> 
> For intA = 1 To 14
>     numAve = rst.Fields!Close + numAve  { When run, can't find record }
>     intB = intB + 1
>     If Not rst.EOF Then
>     rst.MoveNext
>     Else
>     intA = 15
>     End If
> Next intA
> 
> rst.Bookmark = varBookmark
> numDaysAvg = numAve / intB
> rst.Edit
> rst.Fields!DaysAvg14 = numDaysAvg
> rst.Update
> rst.MoveNext
> Loop
> 
> Set rst = Nothing
> Set db = Nothing
> 
> End Function
> 
> Please help. Thank
> 
> Nelson Chou
0
Utf
6/27/2007 2:08:03 PM
Reply:

Similar Artilces:

Faulty cost basis calculation
I just re-installed Money 2003 Delux afyter a hardware problem, and am using a backup copy of the Money file. I hav eone investment that shows the correct purchase amounts and quantities, but has calculated the cost basis incorrectly. ( i.e. purchase 4000 shares at 2.35, cost basis $52,000 when it should be about $11,000). I can not find any way to edit the cost basis, or a way to get this program to re-calculate it correctly. Anyone else have this problem and solve it? In microsoft.public.money, <anonymous@discussions.microsoft.com> wrote: >I just re-installed Money 200...

Show image for specific record on report based on yes/no value
Hi, We have a product database which has several yes/no categories for each record, I would like to be able to show an image on the report based on which yes/no boxes are ticked. So, we have a list of plants in our database that have yes/no columns named "Sun", "Shade" or "Semi-Shade" (depending on what aspect they like) I have a different image for each option (named the same .jpg) and I would like the relevant image to show up on the report for each record if it is true, so the report is easier to scan with images rather than text. Any ideas on how I could do t...

Question about total cast basis calculation for an investment acco
I am using Money 2004 to track my mutual fund investments, among other things. I had the portfolio manager options set to show closed positions. When configured as such, the the cost basis from the closed positions was added into the total cost basis for the investment. In my case, the closed positions were a result of a non-taxable sell and reinvest into another investment. Long story short, some money shows up twice in the total cost basis calculation. After clearing the option to show closed positions, the total cost basis calculation appears to be correct. I would expect to see...

#VALUE! error: vlookup works in Excel 2000 but not 2003
Hi, My client has a spreadsheet which works fine in Excel 2000 but when opened with Excel 2003 it populates the pages with #VALUE! and the best I can tell is that the problem is with the following formulas. Can anyone advise if they have seen this before. I am unable to post their file but this is the code that I think is a bit suspect. =IF(ISERROR(VLOOKUP(B14,'C:\Documents and Settings\User\My Documents\[Price List 040301.xls]Items'!$A:$M,11,FALSE))=TRUE,"",VLOOKUP(B14,'C:\Documents and Settings\User\My Documents\[Price List 040301.xls]Items'!$A:$M,11,FALSE...

Tables to PDF
Hi there, I'm working with a table in Publisher at the moment & have formatted all my column / row gridlines to 0.25pt. However, when I convert to Adobe PDF, some of the lines appear much thicker than the others. Any Suggestions? Ian Have you tried printing the .pdf file? I've seen lines that look thicker but print just fine. -- JoAnn Paules MVP Microsoft [Publisher] "ianp" <ianp@discussions.microsoft.com> wrote in message news:402A32B6-8157-4D23-907A-9C437BD4FE35@microsoft.com... > Hi there, > > I'm working with a table in Publisher at...

Net Worth increase calculated incorrectly?!
I've noticed what seems to be a bug to me. Let say for example that my net worth was negative $5,000 and then the next month it became positve $7,500, money then says on it's monthly report that my net worth went up 25% in the last month. Using some sort of logic, wouldn't it be more like 225% ? "Dan Andersen" <isdanreally@hotmail.com> wrote in message news:ux63x34cEHA.2504@TK2MSFTNGP12.phx.gbl... > I've noticed what seems to be a bug to me. Let say for example that my net > worth was negative $5,000 and then the next month it became positve $7,500, ...

How do I show summary totals from a pivot table on a bar chart
I created a pivot table which shows the both individual and grand totals based on the fields I selected. I then created a bar chart based on this pivot table. What I would like to do is show the grand totals as well as the individual totals. Is this possible, if so how do I do it? When I try this (link to the pivot table's summary total) I get an error saying not a valid function. The function line contains =GETPIVOTDATA("Personnel no.",'P2'!$A$3) "Jon Peltier" wrote: > Colleen - > > You can add textboxes to the chart, which are linked to works...

Default value for a field in the create form
Hi all, It's possible to define a default value for a field, in the create form ? Thanks, Hugo You can default a picklist value with out of the box customization but that's it. >-----Original Message----- >Hi all, > >It's possible to define a default value for a field, in the create form ? > >Thanks, >Hugo > > >. > ...

Finding values in different rows/columns
This should be simple, but I'm lost. To greatly simplify my sheets: 1 11 12 13 14 15 21 22 23 24 25 31 32 33 34 35 41 42 43 44 45 2 11 12 13 14 15 21 22 23 24 25 31 32 33 34 35 41 42 43 44 45 I want to do a VLOOKUP(1,A1:A10,1,false) and return the value in D4, or VLOOKUP(2,A1:A10,1,false) and return the value in C9. In other words, find my A cell reference and return the value in a different row/column from that. Another question: When I move a cell (cell1) to another cell (cell2) a cell (cell3) that references cell1 moves its reference from cell1 to c...

fixing pie chart colors depending upon value ?
This is really about using an excel chart inside an access report but nobody in microsoft.access.reports seems to know the answer. Using a grouped field in a database I end up with two values per record showing the number of 'goods' and the number of 'bads' The pie chart plots these two numbers. I want the 'good' slice always to be green and the 'bad' slice always to be red. Setting the format worked OK until I had 7 goods and zero bads, then the whole chart was red instead of green. Any idea how I can fix this? Howard The technique described in thi...

Sales transactions Tables activity
I have Great Plains 9.0 with Service Pack 3 and am looking for a quick explanation on Sales Transactions table entries. Is there any way someone can quickly go over what exactly happens on a Sales Order during the Sales Transaction process. From placing an order, to picking it, to fulfilling it, to invoicing and creating a back order. My issue is that we have moved from printing Pick Tickets at time of order entry, to having our Warehouse print the pick tickets once an hour. The problem is that some Pick tickets don't print, and we cannot determine why. The reps who have entered the o...

MS Access Copy record to another table based on a form input 10-08-07
Hello. I have 6 Tables, all with identical fields. I want to copy a record from one table into another table, which table depends on what table is selected in a form. So if the table names are One, Two, Three, Four, Five, Six, and the record that I want to copy and insert into another table has been selected by a query in a form. I want the form to direct where to insert the table. So if the form has table Two selected (from the drop down list) then it should insert the record into table Two. If the form has table Four selected then it should insert it into table Four. The table the record is...

Select values from list
Hi all, I got a very stupid problem, but like any stupid problem is bothering me... Some time ago I created a Worbook, and in a few columns of a worksheet I created a kind of combobox, integrated in the sheet, to help the user to select values from a list positioned elewhere in the sheet. To put it simple, when the user selects a cell, appears a down arrow on the right side, and clicking it it's shown a list to choose from. The values of the list are in a cell range on the right side of the sheet. Well, now I need to add new columns, but I can't find out how to re-create these co...

How to create and set values for newly created attributes in active directory
Hi, I need to add a new string attribute in the properties of users in the active directory. I have changed the schema and added the attribute. Then, it gets listed in the attributes list. But, I could not set this attribute value for users. Please help me in setting this new attribute value for each and every user of users in AD. Thanks, - mv. -- m v ramana ------------------------------------------------------------------------ m v ramana's Profile: http://forums.techarena.in/members/181470.htm View this thread: http://forums.techarena.in/active-directory/129...

Calculating entries in a group
I have a database that lists all applicants, and I have a report that sorts them according to which Institution they are attending. How can I have the report display the number of applicants that are attending each Institution. For example , Four applicants from Harvard, 10 Applicants from Columbia, ect. Use this query --- SELECT YourTable.[Institution], Count(YourTable.[Institution]) AS [CountOfInstitution] FROM YourTable GROUP BY YourTable.[Institution]; -- KARL DEWEY Build a little - Test a little "amandap83" wrote: > I have a database that lists all applicants, and ...

Printing
Hello... I have looked everywhere, and no answers exist. I am using a Doc/View setup and i want to print. By default, the Print, Print Preview, Print Setup menu commands exist in the File menu. 1) I hit Print Setup 2) Set the paper to 3"x5" Now, How do i get that information back in the CView::OnDraw(CDC* pDC) method (when not printing, but just repainting the view)? Whenever i query PHYSICALWIDTH, HORZRES, VERTRES, etc. using CPrintDialog::GetDefaults()'s HDC, I get the defaults... which is what the settings are before you change them in the Print Setup dialog. I would lik...

How do i calculate a 50 moving averager?
When generating a trendline, the original data still remains. Is there a way to get rid of the data (other than changing the color to white) and simply having the trendline? I was thinking if it is not an option i can calculate the moving averager and merely graph that column. Let me know what is the best way to go about this....Thank you I don't think you can maintain the trendline if you remove the underlying data. You can simply create a new column to graph which is the moving average by using the average command and then dragging it down. By this I mean lets say you had a ...

Pivot Tables
When a pivot table has 2 indices, the values of the first index ar left blank on the 2nd and subsequent lines of the 2nd index. Can thes index values be automatically duplicated? Thanks ANdrew Middleto -- Message posted from http://www.ExcelForum.com Andrew, Send me your email address and I'll PM you a small add-in I put together to do this.. Cheers, Dave >-----Original Message----- >When a pivot table has 2 indices, the values of the first index are >left blank on the 2nd and subsequent lines of the 2nd index. Can these >index values be automatically duplicated?...

Table Import Error
Good Afternoon, My company has recently upgraded from 6.0 to 8.0. I am importing an .csv file and then printing a report. The import seems to be working correctly, what I mean is I receive a message saying it completed. However when I try to pull up the report I receive the following: TABLE IMPORT ERROR That's it no other information, no other users seem to be having this problem, just myself and I get this error on other reports as well, but they all seem to be custom or modified reports from modified or customized forms. Any suggestions? It sounds like your Forms and Reports d...

Ignore table formatting when importing data using OLEDB
I am using Excel 2010. I want to write a macro in a 'consolidate' XLSM file that will query another selected XLSM file and import data from one of the range names in that file, into the 'consolidate' file. When the import occurs, it formats the imported table using the default Excel 2010 table style (see screenshot), however i don't want to format the cells - i just want to import raw data and leave the cells formatted as they are in the 'consolidate' XLSM file. The PreserveFormatting property has no impact, as the damage is already done once the query is added. ...

Add pecent and value on the same graph
I would like to add the percent and the value and display it within a pie chart. Thanks for your help. You can calculate the percentages on the worksheet, and use a formula to create the labels. For example, with the following data in in A1:B3 -- Jan 25 Feb 25 Mar 50 Enter the following formula in cell C1, and copy down to C3: =B1/SUM($B$1:$B$3) To calculate the text for the pie chart labels, enter the following formula in cell D1, and copy down to D3: =B1 & " " & TEXT(C1,"0%") When you create the pie chart, in step 2 of the Chart Wizard, click ...

showing repeating values in the rows of a pivot table
The default setting in pivot tables results in showing the repeating value of a row header in the first row only. Is it possible to allow the repeating value to populate all the cells to which it applies? Not while it's still a pivottable. I'll usually convert to values (or make a copy of that pivottable and convert that copy to values). Then I'll use techniques like those at Debra Dalgleish's site to fill those empty cells: http://www.contextures.com/xlDataEntry02.html kdschreck wrote: > > The default setting in pivot tables results in showing the repeating valu...

VBA
I am running a make table query and the query does not run if the table is open. How can I programmatically check to see if table is open and close it if it is open? Thank you so much for helping. "Sue" <sea_099@hotmail.com> wrote in message news:bd7c8b2a-b7a4-4afc-9774-99cb33fed0da@m25g2000yqc.googlegroups.com... >I am running a make table query and the query does not run if the > table is open. How can I programmatically check to see if table is > open and close it if it is open? Thank you so much for helping. You say your question is about VBA and you ...

Locating Pivot Table in VBA
I have a macro created in VBA that creates two Pivot Tables, one under the other. I works fine except I run into problems when the first table shows up with more rows than I expected when, in the VBA code, I set the table destination of the second table. Then the macro doesn't do well at all. I there a way I can set the table destination of the second table relative to the bottom of the first table? A statement something like this: (preceding stuff omitted).CreatePivotTable TableDestination:="[Workbookname.xls]SheetName!'Three rows below the table above" and so f...

Macro to find value in worksheet
I've been asked if I could come up with a macro or vba that I could enter a list of names in the code and run the macro to find any matches of the names in the open worksheet. I'm a newbie at this and I'm lost. Is there anyone who could help me with this code? It seems like such a simple task but I know better. I'd have to enter about 100 names individually and do a find every day to see if certain names were in this sheet. The sheet changes every day, the names don't. I'd rather have the names hard coded in the macro and just run it and have it pop up a message say...