How to subtract in Access

[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?/

[Jim Allensworth]

Re: How to subtract in Access

On 1 Apr 2004 14:06:20 -0800, whbensoniv@yaho o.com (WHB) wrote:
[color=blue]
>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?/[/color]
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

[Jim Allensworth]

Re: How to subtract in Access

On Thu, 01 Apr 2004 22:27:39 GMT, "Jim Allensworth"
<jimNOT@Notdata centricsolution s.com> wrote:
[color=blue]
>On 1 Apr 2004 14:06:20 -0800, whbensoniv@yaho o.com (WHB) wrote:
>[color=green]
>>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?/[/color]
>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.
>[/color]
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.StockDateDat e & "# " _
& "AND StockID=" & Me.StockID _
& " ORDER BY StockDate DESC;"

Set db = CurrentDb
Set rstLast = db.OpenRecordse t (strLast)
With rstLast
Change = Me.Close - Nz(.Fields(0),0 )
.Close
End With
Set rstLast = Nothing
Set db = Nothing
-----------------------------------


- Jim