Re: Count
- From: "[MVP] S.Clark" <steve.clark_NOSPAM@xxxxxxxxxx>
- Date: Mon, 13 Feb 2006 09:55:49 -0500
=IIF(DCount("*", "qryJobs")=1, DLookup("ProjNo","qryJobs"), "M")
--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html
"Ivor Williams" <ivor@xxxxxxxxxxxxxx> wrote in message
news:jA6Hf.475986$2k.25741@xxxxxxxxxxx
I've created a query called qryJobs. Following is the SQL view of this
query:
SELECT tblMaterial.POID, tblMaterial.ProjNo, Count(tblMaterial.ProjNo) AS
Jobs
FROM tblMaterial
GROUP BY tblMaterial.POID, tblMaterial.ProjNo
HAVING (((tblMaterial.POID)=[FormsPO]![txtPOID]));
I've created a second query called qryJobCount which uses as a record
source the qryJobs query. Following is the SQL view of the qryJobCount
query:
SELECT qryJobs.POID, qryJobs.ProjNo, Count(qryJobs.Jobs) AS CountOfJobs,
IIf([Jobs]=1,[ProjNo],"M") AS Suffix
FROM qryJobs
GROUP BY qryJobs.POID, qryJobs.ProjNo, IIf([Jobs]=1,[ProjNo],"M");
I've created a report based on the qryJobCount query. On the report is a
text box. If there is only one record in the qryJobCount query, I want the
value in the qryJobs.ProjNo to be displayed in the text box. If there is
more than one record in the qryJobCount query, I want the text box to
display "M".
Where I run into a problem is when there is only one record in the
tblMaterial table with the ProjNo field. In that situation, the
qryJobCount query returns more than one record. All I want is one record
which will display either a ProjNo value or "M".
How can I resolve this problem?
Ivor
.
- References:
- Count
- From: Ivor Williams
- Count
- Prev by Date: Re: Create query from list in a table
- Next by Date: Re: Non-Equal values in Queries
- Previous by thread: Count
- Next by thread: RE: Help with update subquery
- Index(es):
Relevant Pages
|
