Solved

Report and Public Function

Posted on 2011-03-09
14
348 Views
Last Modified: 2012-05-11
Experts,

I have this report that returns the Grand Total Sum.
This Grand Total Sum is needed on other reports/forms.

I am thinking that if I want this Grand Total Sum available as a field on other reports / forms I will need to develop a Public Function.  

What I am trying to do is develop a Availability report and I first must start out with the Grand Total Sum of all records.  The SQL below contains the necessary information.  I reallly dont know if I do need a Public Function but I think it would facilitate the development of an Availability Report .

What is an experts opinion on being able to call this Grand Total (See SQL and screen print) and making it available on other reports?  
 
The following is the SQL of the report that returns a Grand Total that I would need available on other reports.  

This is the formula that I use in the field that returns Grand Total Sum (it is circled on the report):
Sum(IIf([CurrencyID]=1,[SumOfAmount],([SumOfAmount]*[ExchangeRate])))
and this is the Grand Total I will want to use on other reports.  

I hope it is clear.  Please ask questions if not.  Thank you
GrandTotal
SELECT tblCurrencyExchange.CurrencyName, Sum(tblLetterOfCredit.amount) AS SumOfamount, tblCurrencyExchange.ExchangeRate, Count(tblCurrencyExchange.CurrencyID) AS CountOfCurrencyID, tblCurrencyExchange.Currencyid FROM tblCurrencyExchange RIGHT JOIN tblLetterOfCredit ON tblCurrencyExchange.CurrencyID = tblLetterOfCredit.Currency WHERE (((tblLetterOfCredit.LCType)<>1 And (tblLetterOfCredit.LCType)<>11 And (tblLetterOfCredit.LCType)<>12) AND ((tblLetterOfCredit.DateOfIssueSB) Is Not Null) AND ((tblLetterOfCredit.ExpiredYN)=0)) GROUP BY tblCurrencyExchange.CurrencyName, tblCurrencyExchange.ExchangeRate, tblCurrencyExchange.Currencyid;

Open in new window

0
Comment
Question by:pdvsa
  • 6
  • 4
  • 2
  • +2
14 Comments
 
LVL 11

Assisted Solution

by:donaldmaloney
donaldmaloney earned 167 total points
ID: 35092289
in modules set a global variable as say glblGrandTotal as long.
On the On Close event of the report

If the GrandTotalName in the report properties is numGrandTotal
glblGrandTotal = me.numGrandTotal


Then the amount is avaiable throughout the application.
Test this by setting a stop in the on close property of this report.
and in the immediate window
type ? me.mumGrandTotal
and then f8 to step throug the  glblGrandTotal = me.numGrandTotal line
then in the immediate window ? glblGrandTotal nad it should be the grand Total on the report
0
 
LVL 6

Assisted Solution

by:TinTombStone
TinTombStone earned 333 total points
ID: 35093525


Why not just use the built in DSum() function?

in your GrandTotal text box, enter the Control Source: =DSum("[FieldName]", "[TableName]")

This could be wrapped up into a function

Function GetGrandTotal()

      GetGrandTotal = DSum("[FieldName]", "[TableName]")

End Function


Then your Control Source would be

=GetGrandTotal()
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 35094433
If you use a global/public variable to contain your value, and you've assigned a value to this variable and you wish to use it again.
Create a function
function get_YourGlobalVariable() as long      '-- or string, or whatever it is
get_YourGlobalVariable = YourGlobalVariable
end function

Now, to use this variable ANYWHERE (a query column, a control source for a text box, etc.) use this syntax:

=get_YourGlobalVariable()

PS: You simply cannot use = YourGlobalVariable in most places... but you can call a function that returns that value.

Scott C
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35096407
In a overall sense, the "total" should be pulled from the data, not the report specifically.

For example, you have a Report that shows the Total of all USA sales.
You have a similar report for all other Countries.

This means that the total for each country (The Grand total from each report), is needed in the next report, to eventually get the Grand Total for all countries.

So instaed of looking at this as taking the "Total for each Report and using it in other reports", you should pull the all of this from the data.
So, in my above scenario, all Sales are stored in the Sales table.
So to get the total for any country you can do something like this:
=Dsum("Frieght","tblSales", "Country='USA'")
Or in a query...
SELECT Sum(Freight)
FROM tblSales
WHERE Country='USA'

To get the total for all Countries use what TinTombStone posted above
Or
SELECT Sum(Freight)
FROM tblSales

Now, all this being said, what I have posted here may not be an exact parallel to your situation (it might even be repetitious of what other experts have posted)...

My point is that you should not be thinking in terms of "Taking a value form one report and using it in another report", but rather simply calculating these values from the data directly.

;-)

JeffCoachman

0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 35096640
ClarkScott,

Right about that if you want to use the global variable in say the default properties of a field in a report/form etc.

pdvsa,

If you had a global variable say glbReportTotal
you can also get the value by calling a  functon like below
so = GetglbValue("glbReportTotal")   will return the total

Put This is in modules:

Global glbReportTotal  As Long

Function GetgblValue(inval As String)
    GetgblValue = Val(inval)
End Function

and put in the report Close Property

 Private Sub Report_Close()
glblGrandTotal = me.numGrandTotal  ' now the total is available throughout the app
End Sub
0
 

Author Comment

by:pdvsa
ID: 35098149
Hi Donald: so i wld have to first open this report correct?  Not sure if i follow.

Tintomb:  this is what i thgt i wld hv to do.  Using the sql from above does it look difficult?

Thank you very much
0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 35109168
Open the report in design view to add this code to the ON Close property event.

 Private Sub Report_Close()
glblGrandTotal = me.numGrandTotal  ' now the total is available throughout the app
End Sub

Go to modules and add a New module :

Global glbReportTotal  As Long

Function GetgblValue(inval As String)
    GetgblValue = Val(inval)
End Function

And save your DB

Now wherever you want to use the Report Total value
just
 put in    
=GetglbValue("glbReportTotal").

TinTomb,  it looks as if he is selecting amounts based on criteria which may not be available il later reports or form since the report query or table may not be open.
Don
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:pdvsa
ID: 35110020
Donald, thank you for the info.... Much appreciated.  I have a question in regards to how the calculation is updated.  Would i have to reopen the report to update the calculation?  

Thank you
0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 35110827
pdvsa,
Yes since the glbReportTotal is set when the report closes.
You could also use the formula given by TinTombStone.
Copy the query Sql  "Select ........."
Make a New query   -  go to sql view and paste the query.
go to design view and you should have the query in place Save it as Say   qryGrandReportTotal
Then you could use TinTobstones suggestion   I have it as :

dsum("[SumOfamount]","qryGrandReportTotal")    'If Sum of amount is the Outstanding Amount Column

BUT
If any field was updated or changed or change while you or anyone else is running the applicatio this will give a NEW total and will not match the total in the report.

So the ONLY way to make sure the values are the same throughout your application and all reports balance to each other is to capture the total on the report close property.
Don
0
 

Author Comment

by:pdvsa
ID: 35113305
Donald, but wouldnt this require me to continually open and close the report?  Not sure if i am following... Sorry... I am far from expert
0
 

Author Comment

by:pdvsa
ID: 35115900
Jeff: i missed your post... Sorry. (i am typing from phone and it is getting difficult to see everyones posts here because if the small window)  

I think i agree with you that instead of relying on opening and closing a report, i should develop a function.

I think my solution is a function.  
The function would be like the Sql in my initial post with currecyid=1 etc... (on phn now....difficult to type)

Maybe i am being repetitive here.  


It seems as though Dons make qry and save suggestion is what i should use:

~~~~~~~~~~~~~~
 Make a New query (this wld be pdvsa initial SQL)  -  go to sql view and paste the query.
go to design view and you should have the query in place Save it as Say   qryGrandReportTotal
The
n you could use TinTobstones suggestion   I have it as :

dsum("[SumOfamount]","qryGrandReportTotal")    'If Sum of amount is the Outstanding Amount Column


Or do i use TinTombs:
~~~~~~~~~~,~~~~~~~~~~~
Why not just use the built in DSum() function?

in your GrandTotal text box, enter the Control Source: =DSum("[FieldName]", "[TableName]")

This could be wrapped up into a function

Function GetGrandTotal()

      GetGrandTotal = DSum("[FieldName]", "[TableName]")

End Function

Then your Control Source would be

=GetGrandTotal()
~~~~~~~~~~~~~~~~~~~~~~~~~~~~


I am
0
 

Author Comment

by:pdvsa
ID: 35115919
I thk dons suggestion incorporated TinTombs i see... I need to get to a computer to see better).  Bare with me....
0
 
LVL 6

Accepted Solution

by:
TinTombStone earned 333 total points
ID: 35116816
Hi pdvsa

In the end, it depends on what you want to see at the bottom of your report, but however you do it, it will require a calculation to be done at some point

Holding a value in a Global field is not a flexible solution because if your data changes then the global value will be wrong.

It is much better to calculate the totals for a report, on the report itself. DSum() is a good tool if the total you want, is not based directly on the reports data (as you suggested)

If the total is based on the report data, then a standard =Sum([Report Field]) in the report footer will do the trick
0
 

Author Comment

by:pdvsa
ID: 35117487
Ok I think I have it now.  

thank you for the help and baring with me.  

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now