Solved

Sum help

Posted on 2013-12-05
5
301 Views
Last Modified: 2013-12-05
Experts,

I have a calculated field in the control source of a report:
USD Equivalent: IIf([tblCurrencyExchange].[CurrencyID]=1,"",Format(([tblFXRates]![Amount]*[tblCurrencyExchange]![ExchangeRate]),"Currency"))


I need to sum this USD Equivalent but for each [projects].[ID]
I have an idea how to do it but something is not correct.  
I can not sum it on the report face (doesnt allow since it is calculated)...need to sum in the query design window.

thank you...let me know if you need additional info.
0
Comment
Question by:pdvsa
  • 2
  • 2
5 Comments
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 500 total points
ID: 39698336
Use the Report Grouping option to Group on [projects].[id] then sum your field in the footer for the group.

ET
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39698362
Try grouping your report by projects.ID.  Place a textbox in the group header or footer section, and set it's control source to:

 = Format( SUM( ABS([tblCurrencyExchange].[CurrencyID]=1) * [tblFXRates]![Amount]*[tblCurrencyExchange]![ExchangeRate]), "Currency")

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39698369
Or possibly this, and do the formatting through the textbox properties instead of in the expression:

 = SUM( ABS([tblCurrencyExchange].[CurrencyID]=1) * [tblFXRates]![Amount]*[tblCurrencyExchange]![ExchangeRate])

Open in new window

0
 

Author Closing Comment

by:pdvsa
ID: 39698375
thanks.  It works.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39698396
Glad to help and thanks for the points.

ET
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

895 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

13 Experts available now in Live!

Get 1:1 Help Now