55
In Access 2000, I'm trying to create a couple reports but can't figure out the queries.
1. In a form, I have a control with a dollar amount, with the source field [Price]. Then there's an option group to indicate if the amount is 1) estimated or 2) firm, with the source field [EstFirmAmt]. On my report, I want to have one control that shows the dollar amount for Estimated Price and another for Firm Price. I thought I could create new fields in the query called PriceEst and PriceFirm. I tried this:
PriceEst: Iif(([EstFirmAmt]=1),[Price],0).
I got #Error. I don't know whether the 0 needs to be "0"... actually, I'd rather it be simply Null. So I don't know how to end that statement correctly. But no matter what I tried, I still got #Error.
So my first question is how to set up this query.
2. I need a report to count over a specified period of time the number of bids requested, submitted, and awarded. The problem is that each of these is related to a different date field in the table. "Bids requested" relates to the date an internal number was assigned to the bid: AssignedDate. "Bids submitted" relates to SubmitDate. "Bids awarded" relates to AwardDate. So I'm trying to create a single report that shows 1) number of records with AssignedDate between, for instance, 1/15/08 and 2/29/08 (dates to be specified by the user running the report), 2) number of records with SubmitDate in the same date range, and 3) number of records with Award Date in the same date range. And finally, I'd like the heading of the report to say "Bid Totals Between 1/15/08 and 2/29/08".
-I've tried creating a query, but I can't figure it out.
-I've tried creating calculated fields in a report based on the table. For instance, for bids submitted, I tried:
=(SELECT Count([Bids Table].ID) AS CountOfID FROM [Bids Table] WHERE ((([Bids Table].[SubmitDate]) Between [What start date?] And [What end date?])))
The report doesn't ask me for "What start date" and "What end date", it shows #Name? in the control, and it appears to show each record separately, rather than one counted total.
-I've also tried creating 3 subreports to stick in one main report with no record source. For this, I end up answering my questions "What start date?" and "What end date?" four times each, and although the subreports work, I get #Name? for the dates in the report header.
So if anyone has an idea for either or both of these reports, I would be grateful!
Angi
