count is a sql term and used to count rows in a query
dcount is a vba function which access sql can use
there are other ways to do it using just sql
However just realised the dcount criteria is incomplete (did not include a date element) so this is the corrected one
Code:
SELECT *, dcount("*","myTable","VendorId='" & VendorID & "' AND ProjectID='" & ProjectID & "' AND ProductID ='" & ProductID & "' AND format(creationdate,'yyyymm")='" & format(dateadd('m',-1,date),'yyyymm') & "'") AS ExistsPriorMonth
FROM myTable
WHERE format(creationdate,"yyyymm")=format(date,"yyyymm" )
and this is one just using sql
Code:
SELECT *, Exists(SELECT * FROM myTable X WHERE VendorId='" & VendorID & "' AND ProjectID='" & ProjectID & "' AND ProductID ='" & ProductID & "' AND format(creationdate,'yyyymm")='" & format(dateadd('m',-1,date),'yyyymm') & "'" ) AS ExistsPriorMonth
FROM myTable
WHERE format(creationdate,"yyyymm")=format(date,"yyyymm" )
or another way
Code:
SELECT DISTINCT A.*, iif(isnull B.ID,True,False) AS ExistsPriorMonth
FROM myTable A LEFT JOIN myTable B ON A.VendorId=B.VendorID AND A.ProjectID=B.ProjectID AND A.ProductID = B.ProductID
WHERE format(creationdate,"yyyymm")=format(date,"yyyymm" ) AND format(B.creationdate,'yyyymm")= format(dateadd('m',-1,date),'yyyymm')
note these are all 'air code', you will need to correct for table and field names and perhaps typo's on my part