Counting a value on a form from all records in a table.

[isaace]

I am trying to enter a control on a form which can count the total number of entries with a particular value. For example, I have a field on my form which holds a cell phone number. I would like to create a separate field which counts the total number of entries with the same cell phone number. This will help me from entering duplicate records. I know how to count the actual field; =count(cellphone). I found some people online mentioning to use the sumif with the count. The problem is I cannot get the correct syntax. Currently I have = sumif(count(cellphone = me!cellphone,1,0) but I am getting syntax errors; missing parenthesis. Can anyone help? is this possible?

[Seth Schrock]

Well, the missing parenthesis error is because you are missing an ending parentheis at the end of the expression. Each parenthesis must be paired up with another so that there is a starting and ending parenthesis. I'm not familiar with the SumIf() function, but you would at least need to make it =SumIf(Count(CellPhone = Me!CellPhone,1,0)). Also, I don't think that you can use the Me! reference in the textbox. You would have to use the full reference.

My recommendation to solve your problem would be to use DCount(). To make it so that it is easy to refresh it, I would put this in the control's OnClick event:

Me!cellphone = DCount("*", "enter the table name here", "Cellphone = " & Me!cellphone)

[zmbd]

Unless it is a subform, you should be able to refer to the controls directly by name:
Me!cellphone = DCount("*", "enter the table name here", "Cellphone = " & [cellphone])