By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,617 Members | 1,166 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,617 IT Pros & Developers. It's quick & easy.

Form/TextBox/Count Function problem

Lensmeister
P: 15
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.
Mar 6 '09 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
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
Mar 6 '09 #2

Lensmeister
P: 15
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.
Mar 8 '09 #3

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. DCount("[name of some field]", "[your table name]", "[field you want to compare] = 'W*'")  ' < string form
  2. DCount("[name of some field]", "[your table name]", "[field you want to compare] = 123456) ' < numeric form
-Stewart
Mar 9 '09 #4

Lensmeister
P: 15
Hi,

I have the Unbound form with a textbox on it.

Expand|Select|Wrap|Line Numbers
  1. =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.
Mar 9 '09 #5

Expert Mod 2.5K+
P: 2,545
If what you have posted is accurate you have the closing double quote in your WHERE clause in the wrong place:

Expand|Select|Wrap|Line Numbers
  1. =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
Mar 9 '09 #6

Lensmeister
P: 15
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 .... :) :) :)
Mar 9 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.