0

Hello I have been trying to return the result of a DCount within an MS Access form. I have a field called "Process" which stores a process such as "Shipping" and Sub-Process field which stores text too e.g. "Engineering". I have another field which needs to store the count of how many shipping processes have a specific sub-process such as answering "how many sub-processes are there for each process?" called txtcountsubprocesses.

The related text field is saved as txtprocess and the count field (the one I can't seem to get right) has the name txtcountsubprocesses. In the Control Source property for txtcountsubprocesses I have the following expression: 

=DCount([Sub Process],[LogisticsData],[Process]=txtprocess.text)

I receive the following error message: #Name?

I have tried multiple other ways of programming the DCount however the way mentioned above seems to be the closest I have to a possible answer. I have also checked the spelling of the table headers from [LogisticsData].

Is it possible to solve my problem using a DCount method like this?

Required output (sorry not sure how to put in a proper table):

Process ; Sub-process ; Sub-processCount (3 columns) Shipping ; Engineering ; 5 (three pieces of related data - the number being what I need to produce) Shipping ; Medical ; 4 (three pieces of related data - the number being what I need to produce)

Matt
  • 149
  • 3
  • 14

1 Answers1

1

Firstly, DCount needs string parameters to identify field and table name.

Secondly, if you want to include something from the current form, you can concatenate those strings, or use a form parameter.

Thirdly, you should almost never use .Text. Use .Value instead. You can only use .Text on controls that have focus.

So, with all these things fixed:

=DCount("[Sub Process]","[LogisticsData]","[Process]='" & txtprocess.Value & "'")

You might want to read into using parameters, since oddities might occur when the value is Null or contains single quotes

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thank you very much for your excellent explanation and the heads up about the value vs text! – Matt Apr 20 '18 at 18:02