0

I have been having much trouble with this problem. I have a table in Access called "Import" that I import records to. Each record has a facility name that corresponds to a "region" from a different table called "COID_Lookup". I'm trying to get a count of records in the "Import" table based on criteria from the "COID_Lookup" table. Is this possible? Also, I have a query that already does this perfectly but I understand a textbox value cannot be based on a query.

This is what I've tried =DCount("Facility","tblImport","tblCOID_Lookup.Region = 'Midwest'")

My output is #Error in the textbox that blinks as if it is caught in an endless loop.

The query I have, Midwest_Count, works as expected, but I don't know how to put that in the expression. I have tried to look it up but the answers don't make sense to me. I'm sorry.

The solution I used is a DLookup of the query I had that worked. =DLookUp("CountOfFacility","qryMidwest_Count")

This is the query.

SELECT Count([tblImport].Facility) AS CountOfFacility FROM tblCOID_Lookup INNER JOIN tblImport ON tblCOID_Lookup.[Facility] = tblImport.Facility WHERE (((tblCOID_Lookup.Region)="Midwest"));

Christopher Magro
  • 1
  • 1
  • No, that can't be done. The expression makes no sense. Build a query that joins tables and use that query as source for DCount. Or if you already have a query with the count, use that as source and DLookup. Edit question to show sample data as text tables. – June7 Aug 03 '21 at 20:11
  • I do have a query that works correctly but I don't know how to use that as part of the expression. I'm not a programmer or database admin but my boss has made me one anyway. – Christopher Magro Aug 03 '21 at 20:37
  • Thank you. I got it to work with a DLookup function. I appreciate your help. – Christopher Magro Aug 04 '21 at 00:04
  • At least you could reveal your solution for the records and perhaps upvote comments/answers that were helpful. – Gustav Aug 04 '21 at 07:40

1 Answers1

0

Yes, you can do that, as DCount will accept an SQL criteria value:

=DCount("*", "tblImport", "[Facility] = (Select [Facility Name] From tblCOID_Lookup Where [Region] = 'Midwest')")
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • I put this in the control source of the textbox but I still get that #Error as output. – Christopher Magro Aug 03 '21 at 20:47
  • Double-check spelling as well as your data for _Null_ values. – Gustav Aug 03 '21 at 20:49
  • There are no null values. I checked the spelling as you said and I changed "Facility Name" to just Facility as that is the field in the table COID_Lookup. '=DCount("Facility","tblImport","[Facility] = (Select [Facility] From tblCOID_Lookup Where [Region] = 'Midwest')")' To understand correctly, [Facility] = (Select [Facility] From tblCOID_Lookup part means it is comparing "facility" from tblImport with "facility" from COID_Lookup? Please forgive my ignorance. I'm struggling with this assignment from my boss. – Christopher Magro Aug 03 '21 at 21:31
  • 1
    Really should provide sample data as text tables if you want more assistance with this. – June7 Aug 04 '21 at 02:04
  • This won't work if region can have multiple facilities. Causes #Error. – June7 Aug 04 '21 at 02:13
  • This does work: `=DCount("*", "tblImport", "[Facility] IN (Select [Facility Name] From tblCOID_Lookup Where [Region] = 'Midwest')")` – June7 Aug 04 '21 at 02:18