Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access programming

Posted on 2011-09-22
4
Medium Priority
?
317 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
  • 2
  • 2
4 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Six Sigma Control Plans
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

885 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