count in a query

[tallguy804]

This is sooooo very simple i know it is but I just can't figure out what I am doing wrong.

I have a query and I want to count all of the records in the query where the field sex = male. I add the field to the qery and set the total option to "count" That counts all of the records with out regard to what is in the field. When I add "male" to the Criteria i get an error about a data mismatch.

The users are selecting "male" or "female" on the input for from a combo box. How do i get the query to only count the male records?

Thanks

 

[PHV]

You have to know the ID corresponding to "male"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tallguy804]

Can you tell me how to find out what the ID for male is?

 

[PHV]

What is the RowSource of the combo ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tallguy804]

the row source is:

"Male";"Female

 

[PHV]

Is by chance sex a lookup field ?
If not what is it's datatype in the table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tallguy804]

under "lookup" in the database design it has this information:

Display control: Combo box
Row Source: value list
row source: "Male";"Female"
bound column: 1
column count: 1
Column heads: no
colun width: 1"
list rows: 8
list width: 1"
limit to list: no

The fields data type is "text" with a field size of 50

 

[PHV]

Have you tried 1 as criteria ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tallguy804]

yes... didn't work. "1" returns no records exen though there are records that have both male and female in the field

 

[PHV]

What is the result of a query like this (in the SQL pane of the query window) ?
SELECT sex, Count(*)
FROM yourTable
GROUP BY sex;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244