By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

MS Acess 2010 Expression Builder using queries

P: 33
Hi there,

I am currently working on an Access 2010 project and I am having some trouble with the expression builder. I have to populate 5 fields using the sum function. I have attached a screenshot of the report for a better idea.

The following 5 fields (Passenger, Agricultural, Truck, ATV and Forklift) need to be populated the "TotalPTE" sum of each respective tire type.

To do this, I wrote 5 different queries which I use in the Expression Builder to get the SUM of the Total PTE for each.

here is the query:

Expand|Select|Wrap|Line Numbers
  1. SELECT ScrapTireType.PTEamount,
  2.  ScrapTireType.description,
  3.  ScrapCollectionTireType.amount,
  4.  ScrapCollection.regNum,
  5.  ScrapCollection.invoiceNum,
  6.  ScrapCollection.Date,
  7.  ScrapCollection.driverSigned,
  8.  ScrapCollection.retailerSigned,
  9.  ScrapCollection.PTEtotal,
  10.  ScrapCollection.scrapCollectionID 
  11. FROM ScrapCollection 
  12. INNER JOIN (ScrapTireType 
  13.  INNER JOIN ScrapCollectionTireType 
  14.   ON (ScrapTireType.scrapTireTypeID = ScrapCollectionTireType.scrapTireTypeID) 
  15.   AND (ScrapTireType.scrapTireTypeID = ScrapCollectionTireType.scrapTireTypeID)) 
  16.  ON ScrapCollection.scrapCollectionID = ScrapCollectionTireType.scrapCollectionID 
  17. WHERE (((ScrapTireType.PTEamount)=[Reports]![RPTEDetailsEndDate]![amount]) 
  18.   AND ((ScrapTireType.description)="Passenger") 
  19.   AND ((ScrapCollection.regNum)=[Forms]![PTEDetailsNew]![regNum]) 
  20.   AND ((ScrapCollection.Date)<=[Forms]![PTEDetailsNew]![endDate])) 
  21. ORDER BY ScrapCollection.Date DESC;
When I run this query, I get the right data. But when I run the report, I am prompt to enter the amount parameter. Not to sure why. I have also attached my expression from the expression builder and a screenshot of the prompt.

I would really appreciate any help. I don't want to be prompted for the amount when the query returns it with no problem.

Thank you
Attached Images
File Type: jpg ExpressionBuilder.jpg (19.8 KB, 446 views)
File Type: png Prompt.png (9.4 KB, 435 views)
File Type: jpg Report.jpg (23.2 KB, 440 views)
May 28 '12 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 5K+
P: 5,397
Sorry for the delay... when the board is busy, the posts sometimes scroll off the bottom of the list.

The issue is in the WHERE (((ScrapTireType.PTEamount)=[Reports]![RPTEDetailsEndDate]![amount]) of your sql. It is looking for a value in the "Amounts" field of your report. You need to change this line so that it refers either to a value in an open form, a table, or a query.

I can only guess that why you are not prompted for a value when you open the query is because the field is referenced correctly within in the sql.

Aug 27 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.