Solved

Access programming

Posted on 2011-09-22
4
313 Views
Last Modified: 2012-06-21
Ok, I have this access thing I've been working on all day and I cant seem to get the formula right. Or maybe it cant do it the way I want it to.

I have a report based on a query. The query has all the information I'm looking for a needing. scenario is as follows.

Sample report of what it currently does


CustomerName     Order Number    Qty   DaysLate    Order Dollars     Dollars Late
 
ABCD                      6582                   2           30            7,000                  42,0000
Sample                    9324                   1           2              5,000                  10,000
Test                         9499                   1           5              1,000                   5,000

Sum                                                                                12,000                 52,000

What I want it to do:

CustomerName     Order Number    Qty   DaysLate    Order Dollars     Dollars Late
 
ABCD                      6582                   2           30            7,000                  42,0000
Sample                    9324                   1           2              5,000                  10,000
Test                         9499                   1           5              1,000                   5,000

Sum                                                                                12,000                 52,000
9*** Sum                                                                                                     15,000


I want to add a number at the end or another sum line to sum up every order number that starts with a 9. the 15,000 is what I want it to total for me along with everything else.

Question is 1: Do I do this in the report, or do I do this in the Query?
Question 2 is: How would the formula look.

Formula for the Dollars late summary is =Sum([Dollars Late])

I want it something like this =Sum([Dollars Late] IIf([CUST_ORDER_ID]>="9000" And [CUST_ORDER_ID]<="9999"))

I might need to provide more information, but This is a start.
0
Comment
Question by:Keef400
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36583298
Close!

=Sum(IIf([CUST_ORDER_ID] Like "9*", [Dollars Late], 0))
0
 

Author Comment

by:Keef400
ID: 36583350
AHHHH!!!! seems so simple now. I guess I was over complicating it. Thank you very much, and thank you even more for understanding what I was asking for.
0
 

Author Closing Comment

by:Keef400
ID: 36583355
Very quick, Thank you!
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36583394
Glad to help :)
0

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Part One of the two-part Q&A series with MalwareTech.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

622 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