Prev: How do I display calendar month by name, not number
Next: Hate Your Job?
From: Mike DFR on 29 Jun 2008 05:06
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.
From: fredg on 29 Jun 2008 10:19
On Sun, 29 Jun 2008 02:06:00 -0700, Mike DFR wrote:

> 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.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
From: Mike DFR on 29 Jun 2008 15:58
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

"fredg" wrote:

> On Sun, 29 Jun 2008 02:06:00 -0700, Mike DFR wrote:
>
> > 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.
>
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>
 | 
Pages: 1
Prev: How do I display calendar month by name, not number
Next: Hate Your Job?