DCount with Multiple Criteria (1 Viewer)

[ne1983]

Hey
Having an issue with this , any help appreciated!

[tblMaint_Rqst_Request] = table of maintenance requests
[MT_Rqst_Location] = field in table containing location (can be numerical or text)
[MT_Status_Value] = field in table containing status of job as number, ie 1= open, 2 = in progress, ...., 6 = closed.

I want a DCount in unbound text box simply showing how many jobs are NOT CLOSED (ie status < 6) for the same location when looking at a record

Tried the below to no avail
DLookUp("[MT_Rqst_location]","[tblMaint_Rqst_Request]","[MT_Status_Value] < 6 AND [MT_Rqst_Location] = ‘" & Forms![frmMain]![frmMaintDashboard].Form![frmMaintDashboardSUB]![MT_Rqst_location]&"'")

 

[plog]

Tried the below to no avail

Define 'no avail'. Error message? Unexpected results?

With that said the answer is to always divide and conquer. First I would make it a DCount instead of a Dlookup since that's what you want. Then I would remove all the criteria from it. Just get it to return a valid result. Once you have that, start adding criteria back into it one by one until it stops working. The last criterion you added is the offender.

 

[ne1983]

thanks plog, error message = #Error

Tried dcount as well - tried a number of troubleshooting and i think its something to do with the <6 and location references.... I can only get it to return the total number of record which is no good.

 

[plog]

think its something to do with the <6 and location references.

Yeah, divide and conquer to identify.

 

[Minty]

So applying the dividing and conquering try

DCount("[MT_Rqst_location]","[tblMaint_Rqst_Request]","[MT_Status_Value] < 6 "

This should give you your count of Locations not at 6... regardless of the location.