Using Count in a subform

[Peter]

Hi all,

Subform: Bag
Controll: Apples (bound combobox)

I want to Count all records [Apples] that are "green"

This is the Control Source the textfield i want to use to display the amount
of records; =Count([Apples]="Green")

I receive all records...not the selcted "green"...

What am i doing wrong

Thanks for all help!

 

[Piet Linden]

Hi all,

Subform: Bag
Controll: Apples (bound combobox)

I want to Count all records [Apples] that are "green"

This is the Control Source the textfield i want to use to display the amount
of records; =Count([Apples]="Green")

I receive all records...not the selcted "green"...

What am i doing wrong

Thanks for all help!

you're using the wrong function. You want DCount()
first argument is the column you want to count, second is the table,
third is the criteria.
= DCount("PatientID","Patient","[LastName]='James'")

 

[Peter]

Thanks Piet, can i also use a wildcard example "James*" ?

Hi all,

Subform: Bag
Controll: Apples (bound combobox)

I want to Count all records [Apples] that are "green"

This is the Control Source the textfield i want to use to display the amount
of records; =Count([Apples]="Green")

I receive all records...not the selcted "green"...

What am i doing wrong

Thanks for all help!

you're using the wrong function. You want DCount()
first argument is the column you want to count, second is the table,
third is the criteria.
= DCount("PatientID","Patient","[LastName]='James'")

 

[Peter]

I am doing something wrong here...

=DCount("Fruits","Bags","[Apples]='Green")

Column. Fruits
Table. Bag
Criteria. [Apples]=Green

getting error in the textfield...By columns you mean column in the combox?

Thanks Piet, can i also use a wildcard example "James*" ?

Hi all,

Subform: Bag
Controll: Apples (bound combobox)

I want to Count all records [Apples] that are "green"

This is the Control Source the textfield i want to use to display the amount
of records; =Count([Apples]="Green")

I receive all records...not the selcted "green"...

What am i doing wrong

Thanks for all help!

you're using the wrong function. You want DCount()
first argument is the column you want to count, second is the table,
third is the criteria.
= DCount("PatientID","Patient","[LastName]='James'")

 

[Douglas J. Steele]

Your quotes are wrong. You've got a single quote before Green, but not after
it: You need:

=DCount("Fruits","Bags","[Apples]='Green'")

Exagerated for clarity, that's

=DCount("Fruits","Bags","[Apples]= ' Green ' ")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am doing something wrong here...

=DCount("Fruits","Bags","[Apples]='Green")

Column. Fruits
Table. Bag
Criteria. [Apples]=Green

getting error in the textfield...By columns you mean column in the combox?

Thanks Piet, can i also use a wildcard example "James*" ?

Hi all,

Subform: Bag
Controll: Apples (bound combobox)

I want to Count all records [Apples] that are "green"

This is the Control Source the textfield i want to use to display the
amount
of records; =Count([Apples]="Green")

I receive all records...not the selcted "green"...

What am i doing wrong

Thanks for all help!

you're using the wrong function. You want DCount()
first argument is the column you want to count, second is the table,
third is the criteria.
= DCount("PatientID","Patient","[LastName]='James'")

 

[Krzysztof Naworyta]

Juzer Piet Linden <[email protected]> napisa³

|| Subform: Bag
|| Controll: Apples (bound combobox)
||
|| I want to Count all records [Apples] that are "green"
||
|| This is the Control Source the textfield i want to use to display the
|| amount of records; =Count([Apples]="Green")
||
|| I receive all records...not the selcted "green"...
||
|| What am i doing wrong

Count() function counts all records without NULLS in a particular field)
(exception: Count(*) always counts all records)

You can use expresion:

=Count(iif([Apples]="Green";1;null))

or use Sum():

= -Sum([Apples]="Green")

| you're using the wrong function. You want DCount()
| first argument is the column you want to count, second is the table,
| third is the criteria.
| = DCount("PatientID","Patient","[LastName]='James'")

I almost never use D* functions.
They are very slow and very rarely needed.

 

[Peter]

OK, Thanks Piet, Doug and KNi am getting it..step by step.

I will use both options and experiment around some..

Thanks again!