Form/TextBox/Count Function problem

[Lensmeister]

I am getting a little better on this Access DB (in Access 2003) I am making although I am having one problem at this time I cannot fathom out.

I have a form (frm_Stats) This form has the following textboxes on it
1. Total Number of Records in the table Matches.
2. Total Number of Records in the table tbl_Players.

Once I get over these I can move on a bit.

OK the problem:

I have the form and the textbox. In the "control source" I have the following Formula which I found on a website:
=Count([Matches])

when I open the form up it shows
#Error

Anyone have any ideas how to overcome this. I have also tried
=Count([tbl_Players])

Again that came up with
#Error

Thanks in advance.

[Stewart Ross]

The #Error message usually arises when you supply a field name that is not recognised as part of the underlying recordset, or when you have accidentally set the name of the unbound control concerned to be the same as an existing field (which would prevent Access from knowing which one to apply).

Simplest answer, since Count does not need a field name, is to set the control source to be

=Count(*)

This will give you a count of the number of records in the recordset underlying your form. However, if you are using an unbound form (one that is not bound to a query or table) you have nothing to count.

If this is the case you can perform a lookup of the count of the number of records in a table by using the DCount function:

=DCount("*", "tbl_Players")

-Stewart

[Lensmeister]

Hi Stewart,

Thanks for that. It is a unbound form. Can I confirm that if I wanted to cont the number of "W" in the table Matches in the column/Field R the formula would be

=Dcount("W","Matches","V") ? ? ? ? ?

Lensmeister.

[Stewart Ross]

Not quite; DCount (like the Count function) counts the number of rows in the dataset. To count rows that have 'W's (or any other value) in them you need to restrict the rows being counted to be only those where the field or fields concerned have that value. DCount can do this, using its WHERE clause (which is like an SQL WHERE clause without the word WHERE. For a string comparison use the first form of the DCount below, for a number use the second:

DCount("[name of some field]", "[your table name]", "[field you want to compare] = 'W*'")  ' < string form
DCount("[name of some field]", "[your table name]", "[field you want to compare] = 123456) ' < numeric form

-Stewart

[Lensmeister]

Hi,

I have the Unbound form with a textbox on it.

=DCount("[R]","[Matches]","[R]"='W')

is in the 'control source'

In the Matches table there are 2 "W" and only 1 "L". in the column/field "R" (R=Result).

When I view the form it comes up with a "0". I tried to remove the 1st and 2nd [R] but they didn't work either.

Am I missing something ?

Lensmeister.

[Stewart Ross]

If what you have posted is accurate you have the closing double quote in your WHERE clause in the wrong place:

=DCount("[R]","[Matches]","[R]='W'")

As a piece of advice, give your fields meaningful names. [R] is not in any way intuitive or helpful. It should really just be Result...

-Stewart

[Lensmeister]

Thanks mate ... that works perfect.

the field called R is for result, but it's an old Excel database I set up about 12 years ago and I am used to what each one means .... I am a creature of habit so R was the easiest for me at the time.

I will think about getting it changed once I got my head round this DB ..

Again thanks mate .... :) :) :)