Use DCount to specify data with two criteria in a text box

[Mike DFR]

I am trying to count the number of times one type of machine matches with a
selected name.
The name is selected from a drop down combo box, (smith, brown, jones....)
Text boxes are allocated to each machine type, (typeA, typeB, typeC.....)
The query & tables contain fields for 'machine type', and 'person name'
The text box control should return a value for;- how many times machine
'typeA', appears for person 'smith'.
I am using 'DCount' as only the number of rows are required.
I can get a total, but can not get the syntax right when trying to narrow
the search.
Text box control is required, not SQL statement or VB code.
It works as far as; =DCount("*","Masterlist","[Model]"), anything else I try
gives #ERROR.
Thanks for your help.

 

[fredg]

I am trying to count the number of times one type of machine matches with a
selected name.
The name is selected from a drop down combo box, (smith, brown, jones....)
Text boxes are allocated to each machine type, (typeA, typeB, typeC.....)
The query & tables contain fields for 'machine type', and 'person name'
The text box control should return a value for;- how many times machine
'typeA', appears for person 'smith'.
I am using 'DCount' as only the number of rows are required.
I can get a total, but can not get the syntax right when trying to narrow
the search.
Text box control is required, not SQL statement or VB code.
It works as far as; =DCount("*","Masterlist","[Model]"), anything else I try
gives #ERROR.
Thanks for your help.

=DCount("*","Masterlist","[PersonName] = '" & Me.[ComboName] & "' AND
[MachineType] = 'TypeA'")

Change [ComboName] to whatever the actual name of your combo box is
that contains 'Smith'.
Change [MachineType] to whatever the actual name of the Field is that
contains the Type value.

 

[Mike DFR]

Thanks for the help fredg.
I do not get #Error now, instead I get #Name?
I can not see where I have a name wrong.
The form is based on a query called Masterlist, the exact field names are
Tech and Model, one machine is MPS 30, the combo box is Combo58
So the expression reads;
=Dcount("*","Masterlist","[Tech]='" & Me.[Combo58] &"' AND [Model]='MPS 30'")
I type it in exactly as above, but when I leave the box the [] around
Combo58 disappear, so I go in and put them back, then when I change to form
view and back to design view, a pair of square brackets appear around Me and
Combo58, like this; & [Me].[Combo58] &.
Any other suggestions are greatfully recieved.
There has got to be a less frustrating way to spend a Sunday

I am trying to count the number of times one type of machine matches with a
selected name.
The name is selected from a drop down combo box, (smith, brown, jones....)
Text boxes are allocated to each machine type, (typeA, typeB, typeC.....)
The query & tables contain fields for 'machine type', and 'person name'
The text box control should return a value for;- how many times machine
'typeA', appears for person 'smith'.
I am using 'DCount' as only the number of rows are required.
I can get a total, but can not get the syntax right when trying to narrow
the search.
Text box control is required, not SQL statement or VB code.
It works as far as; =DCount("*","Masterlist","[Model]"), anything else I try
gives #ERROR.
Thanks for your help.

=DCount("*","Masterlist","[PersonName] = '" & Me.[ComboName] & "' AND
[MachineType] = 'TypeA'")

Change [ComboName] to whatever the actual name of your combo box is
that contains 'Smith'.
Change [MachineType] to whatever the actual name of the Field is that
contains the Type value.