record counts with specific records

[mim77]

Hi,

I'm very rusty with Access programming. I hope someone can help me. I'm trying to include a count of the number of specific records in a field. Values for the records in the field are either 1, 2, 3, or 4. I just can't seem to come up with the right statement for a report that will give me the number of records with, for example, 1 as the value.
Any ideas?
Thanks!

[JConsulting]

Hi,

I'm very rusty with Access programming. I hope someone can help me. I'm trying to include a count of the number of specific records in a field. Values for the records in the field are either 1, 2, 3, or 4. I just can't seem to come up with the right statement for a report that will give me the number of records with, for example, 1 as the value.
Any ideas?
Thanks!

if you're assigning it to a field,

how about something like

=count(iif([range1]=1,1,0)

[mim77]

if you're assigning it to a field,

how about something like

=count(iif([range1]=1,1,0)

Hi,

Thanks for the idea.
Assuming [range1] is the name of the field in the table, when I use this, I get a prompt to enter parameters. What I really need is a form that provides info that looks like this:

# of records where Overall Performance = 1
# of records where Overall Performance = 2
# of records where Overall Performance =3
# of Records where Overall Performance =4

The Overall Performance data is all stored in one field in a table.

[MMcCarthy]

Try putting textboxes on the form. Set the textbox to

=DCount("*","TableName","[Overall Performance]=1")

[mim77]

Try putting textboxes on the form. Set the textbox to

=DCount("*","TableName","[Overall Performance]=1")

I'm getting a #Error message, and there IS data in the table.

[JConsulting]

Hi,

I'm very rusty with Access programming. I hope someone can help me. I'm trying to include a count of the number of specific records in a field. Values for the records in the field are either 1, 2, 3, or 4. I just can't seem to come up with the right statement for a report that will give me the number of records with, for example, 1 as the value.
Any ideas?
Thanks!

if you're doing this for a report, you can add fields to your report's recordsource (aka the query)

But your question is confusing, are you adding fields to a form or a report?

[MMcCarthy]

I'm getting a #Error message, and there IS data in the table.

Is Overall Preformance a number or a text field?

[mim77]

Is Overall Preformance a number or a text field?

Hi,


It is a number field.

[MMcCarthy]

Try changing it to this

=DCount("[Overall Performance]","TableName","[Overall Performance]=1")

[JConsulting]

Hi,


It is a number field.

Sorry to interrupt.

Where exactly are you putting these calculated fields?

I mentioned earlier that if you're doing this for a report, then you should add them as part of the recordsource....otherwise you can use them in a Group footer. You will not be able to produce a total in the detail section of a report using this method as far as I'm aware.

[MMcCarthy]

Sorry to interrupt.

Where exactly are you putting these calculated fields?

I mentioned earlier that if you're doing this for a report, then you should add them as part of the recordsource....otherwise you can use them in a Group footer. You will not be able to produce a total in the detail section of a report using this method as far as I'm aware.

Not a problem.

JC is right. This code is for a textbox on a form which is what you asked for.

[mim77]

Try changing it to this

=DCount("[Overall Performance]","TableName","[Overall Performance]=1")

Hi,

Now I'm getting an "invalid string" error message.

I'm beginning to wonder now... Does this has anything to do with the fact that my report has an underlying query that is picking out needed data?

[MMcCarthy]

Hi,

Now I'm getting an "invalid string" error message.

I'm beginning to wonder now... Does this has anything to do with the fact that my report has an underlying query that is picking out needed data?

What is the name of your table and what is the name of the field Overall Preformance in the table, exactly?

[mim77]

What is the name of your table and what is the name of the field Overall Preformance in the table, exactly?

The Table name is WDPS Data Table
The field Overall Performance is named Overall Performance

[MMcCarthy]

The Table name is WDPS Data Table
The field Overall Performance is named Overall Performance

then your statment should appear like this

 
=DCount("[Overall Performance]","[WDPS Data Table]","[Overall Performance]=1")
 

[mim77]

then your statment should appear like this

 
=DCount("[Overall Performance]","[WDPS Data Table]","[Overall Performance]=1")
 

It worked!!! Thank you! Thank you! Thank you!

[MMcCarthy]

It worked!!! Thank you! Thank you! Thank you!

You're welcome.