By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,091 Members | 983 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,091 IT Pros & Developers. It's quick & easy.

How to subtract in Access

P: n/a
WHB
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?/
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
- Jim
Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.