Using DCount To Find Statistics

[cmcquain]

I have 1 Text Box on a Form called Pharmacy Info. I am trying to count how many occurances of a field in a table called [Main] and a column called [Pharmacy NPI Number] that match what record the form is currently on. The field on the form that I am trying to match is in a table called [Feedback], column is [NPI_Number]

=DCount("ClaimCount","[Feedback]","[Main]![Pharmacy NPI Number] = [Feedback]![NPI_Number]")

Any help would be great. I have been stuck on this for days. I would also like to get a percentage on a seperate text box if possible.

 

[vbaInet]

Your syntax is off. Here's a link on DCount syntax:

http://www.techonthenet.com/access/functions/domain/dcount.php

=DCount("[COLOR=red]Field in Feedback table[/COLOR]", "[COLOR=red]Feedback table[/COLOR]", "[NPI_Number] = " & [Pharmacy NPI Number])

For text, surround the criteria in quotes.

 

[cmcquain]

Now I am getting a #Name? error in the text box

 

[vbaInet]

So let's see what you wrote?

 

[Bryan]

Now I am getting a #Name? error in the text box

Edit: parallel post.

 

[cmcquain]

=DCount("ClaimCount","Feedback","[NPI_Number] = " & [Pharmacy NPI Number])

 

[vbaInet]

* Which table does [NPI_Number] and [Pharmacy NPI Number] belong?
* What are their data types?

 

[cmcquain]

The [NPI_Number] belongs to the Feedback table and is a text field. The [Pharmacy NPI Number] belongs to the main table and is a text field.

 

[vbaInet]

In that case:

=DCount("ClaimCount","Feedback","[NPI_Number] = " & Chr(34) & [Pharmacy NPI Number] & Chr(34))

 

[cmcquain]

I am getting the same error #Name?

=DCount("ClaimCount","Feedback","[NPI_Number] = " & Chr(34) & [Pharmacy NPI Number] & Chr(34))

 

[Bryan]

I am trying to count how many occurances of a field in a table called [Main] and a column called [Pharmacy NPI Number]

Maybe I'm missing something, but you're trying to count the occurrences in the Main table, right? Shouldn't you be DCounting the [Pharmacy NPI Number] in Main?

 

[vbaInet]

Check ALL the names and ensure they are spelt correctly. Especially check that [Pharmacy NPI Number] exists on your form.

 

[cmcquain]

I am trying to count the occurences of [Pharmacy NPI Number] in the main table that relate to what record the form I am on. The form's control source table is [Feedback]

 

[cmcquain]

Sorry, I should say the record source is [Feedback]

 

[vbaInet]

I am trying to count the occurences of [Pharmacy NPI Number] in the main table that relate to what record the form I am on. The form's control source table is [Feedback]

So it's the other way round like Bryan spotted.

 

[Bryan]

Try starting without the where condition and then add it if it seems to be counting correctly and you don't get errors.

=DCount("Pharmacy NPI Number","Main")

If this is counting all rows in Main, then add the where condition to the end of it.