Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / November 2007

Tip: Looking for answers? Try searching our database.

Subtracting two values in the same field

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jessica - 09 Nov 2007 20:16 GMT
I have created a report based off of a table. In my report I have listed
months, for example in this report I have listed Sept and Oct data. Next
month I will have Oct and Nov. and so on. I am trying to subtract a Sept
value from and Oct value. However, both values I have are in the same field
so I can use [xyz]-[xyz]. Is there a way to subtract these values. I have
attached an example.

TV                 Viewers             Shows           Commercials
Oct                  1365                    62                      105
Sept                 1258                    69                      168
Difference         107                      7                          63  

I need to calculate the Difference field in my report.

Thank You
Allen Browne - 10 Nov 2007 04:42 GMT
One way to do this is to use the events of the report. Store the previous
figures in the Print event of the section, and assign them in the Format
event.

This example assumes your report has text boxes in the Detail section,
named:
-Viewers, Shows, Commercials (bound to fields), and
-txtPriorViewers, txtPriorShows, txtPriorCommercials (unbound) for showing
the previous values.

1. In the General Declarations section of the report's module (at the top,
with the Option statements):
   Dim mlngViewers As Long
   Dim mlngShows As Long
   Dim mlngCommercials As Long

2. In the Format event procedure of the Detail section, add this code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   If FormatCount = 1 Then
       mlngViewers = Nz(Me.Viewers, 0)
       mlngShows = Nz(Me.Shows, 0)
       mlngCommercials = Nz(Me.Commercials, 0)
   End If
End Sub

3. In the Print event procedure of the Detail section, add this code:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
   Me.txtPriorViewers = mlngViewers
   Me.txtPriorShows = mlngShows
   Me.txtPriorCommercials = mlngCommercials
End Sub

One weakness of the above approach is that if you print only some pages of a
report (e.g. starting at page 7), the first record may not have the correct
value. Since it did not print page 6, the value will not have been picked
up, and so won't be shown.

If you don't want to do this in a report, an alternative approach might be
to use subqueries:
   http://allenbrowne.com/subquery-01.html#AnotherRecord

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have created a report based off of a table. In my report I have listed
> months, for example in this report I have listed Sept and Oct data. Next
[quoted text clipped - 12 lines]
>
> Thank You
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.