dcount with two criteria

[Tyler Denmead via AccessMonster.com]

Hi,

I am trying to run a dcount in an unbound text box in a report using two
criteria. One criteria is found in an unbound text box, named [age] in a
form. (In this text box, it uses a formula I found on this site to
calculate someone's age using another field with their birthday.) The
second criteria is found in a field, named [1Q], and can be 1, 2, 3, or 4.
So, I am trying to run a dcount to calculate for example the number of
people with the age "14" and 1Q "1". The name of the form is "Student Data
Entry," with the source of a query being the same name.

Thank you for any assistance. I'm new to access...

 

[Tyler Denmead via AccessMonster.com]

i apologize. I also posted this question in the forms discussion group. I
just figured out how to find it. I thought it wasn't posted...

 

[Tyler Denmead via AccessMonster.com]

but, it would be great if an expert would still be able to answer my
question here!

 

[John Vinson]

Hi,

I am trying to run a dcount in an unbound text box in a report using two
criteria. One criteria is found in an unbound text box, named [age] in a
form. (In this text box, it uses a formula I found on this site to
calculate someone's age using another field with their birthday.) The
second criteria is found in a field, named [1Q], and can be 1, 2, 3, or 4.
So, I am trying to run a dcount to calculate for example the number of
people with the age "14" and 1Q "1". The name of the form is "Student Data
Entry," with the source of a query being the same name.

The three arguments to DCount (actually any of the domain functions)
are:

1. The name of the field to return, or to count, or sum, or whatever
2. The Domain, the name of the table or query wherein the field can be
found
3. An optional search criterion, which is basically the SQL WHERE
clause of a query which selects the desired record or records

This third parameter can have many criteria within it, using AND/OR
logic to connect them.

So something like

=DCount("*", "[Student Data Entry]", "[Age] = " & [Age] & " AND [1Q] =
" & [1Q])

The example you gave would build the string

[Age] = 14 AND [1Q] = 1

This assumes that the table has Number fields name Age and 1Q.

A couple of concerns:

- Storing the Age in a table is unwise; it's better to store the date
of birth and always calculate the age.
- It's probably prudent to rename Form Controls so that they aren't
the same as the name of the table field - e.g. a textbox named txt1Q
bound to the table field named 1Q. That makes it easier for Access
(and you, or me!) to figure out whether you mean the table field value
or the form control value.

John W. Vinson[MVP]