MS Acess 2010 Expression Builder using queries

[Dave0291]

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:

SELECT ScrapTireType.PTEamount,
 ScrapTireType.description,
 ScrapCollectionTireType.amount,
 ScrapCollection.regNum,
 ScrapCollection.invoiceNum,
 ScrapCollection.Date,
 ScrapCollection.driverSigned,
 ScrapCollection.retailerSigned,
 ScrapCollection.PTEtotal,
 ScrapCollection.scrapCollectionID 
FROM ScrapCollection 
INNER JOIN (ScrapTireType 
 INNER JOIN ScrapCollectionTireType 
  ON (ScrapTireType.scrapTireTypeID = ScrapCollectionTireType.scrapTireTypeID) 
  AND (ScrapTireType.scrapTireTypeID = ScrapCollectionTireType.scrapTireTypeID)) 
 ON ScrapCollection.scrapCollectionID = ScrapCollectionTireType.scrapCollectionID 
WHERE (((ScrapTireType.PTEamount)=[Reports]![RPTEDetailsEndDate]![amount]) 
  AND ((ScrapTireType.description)="Passenger") 
  AND ((ScrapCollection.regNum)=[Forms]![PTEDetailsNew]![regNum]) 
  AND ((ScrapCollection.Date)<=[Forms]![PTEDetailsNew]![endDate])) 
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

[zmbd]

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.

-z