On Thu, 01 Apr 2004 22:27:39 GMT, "Jim Allensworth"
<ji****@Notdatacentricsolutions.com> wrote:
On 1 Apr 2004 14:06:20 -0800, wh********@yahoo.com (WHB) wrote:
I am creating a DB for following stocks. The table looks like this:
Date, Hi, Lo, Close, Change, Support, Resistance,
I have it all working, except I want access to calculate the Change column
for me. The change is the difference between the close. So if it closed at
50 yesterday and closed at 55 today - the change would be +5. Conversely,
if it closed and 55 yesterday and closed at 50 today then the change would be
-5. IS there an easy way to do this?/
2 things:
It isn't a good idea to store calculated values. You can always
calculate the difference when you need.
And, Date is a reserved word in Access - not a good idea to use it for
a field name or anything else. It could easily lead to problems when
you later need to code with it.
OK,
for a form or report you could calculate the the Change like ...
Change = Me.Close - DMax("Close", "tblStocks", _
StockDate<#" & Me.StockDate & "#")
Note that I use StockDate for your Date and tblStocks for the name of
the table.
Looking at this again, I believe you would also need an ID for the
stock. And the DMax I suggested won't work. More like...
------------------------------------
Dim strLast As String
Dim rstLast As DAO.Recordset
Dim db As Database
strLast = "SELECT TOP 1 Close FROM tblStocks " _
& "WHERE StockDate<#" & Me.StockDateDate & "# " _
& "AND StockID=" & Me.StockID _
& " ORDER BY StockDate DESC;"
Set db = CurrentDb
Set rstLast = db.OpenRecordset (strLast)
With rstLast
Change = Me.Close - Nz(.Fields(0),0)
.Close
End With
Set rstLast = Nothing
Set db = Nothing
-----------------------------------
- Jim