Count if

  • Thread starter Thread starter Ramone
  • Start date Start date
R

Ramone

I would like help with the following:

is it possible to combine the count and iif function in
the same expression?

I have a table with the following fields: Sales Agent,
State, and Hire Date

I need the count of agents that were hired in each state
based on hire date.

Thanks for any help,
Ramone
 
-----Original Message-----
I would like help with the following:

is it possible to combine the count and iif function in
the same expression?

I have a table with the following fields: Sales Agent,
State, and Hire Date

I need the count of agents that were hired in each state
based on hire date.

Thanks for any help,
Ramone


Ramone,

You might consider using a query to accomplish this.

Open up a new query in design view. Add the table which
contains the data that you want to manipulate. On the
view menu at the top of your page click totals.

Drag the state field to a column in the query and in the
total area select "group by." Drag the Sales Agent field
to another column in the query and in the total area
select "count." If you want to set up the query by date
drag the Hire date to another column and set a parameter
for the date using the criteria area.

Hope all of that made sense.
 
Ramone said:
I would like help with the following:

is it possible to combine the count and iif function in
the same expression?

I have a table with the following fields: Sales Agent,
State, and Hire Date

I need the count of agents that were hired in each state
based on hire date.

How about this, which is even better:

Public Function CountIf(numIn As Variant, num2chk As Variant) As Integer
'********************************************************************
' Name: CountIf
'
' Inputs: numIn As Variant
' num2chk As Variant
' Returns: Long
' Author: Arvin Meyer
'
'********************************************************************
On Error Resume Next

If IsNumeric(numIn) Then
If numIn >= num2chk Then
CountIf = 1
Else
CountIf = 0
End If
End If

Exit_CountIf:
Exit Function


End Function

==================================================
Then in a query column use:

Expr1: CountIf([ColumnToCount], 17)

Where the column to count is ColumnToCount, and 17 is the value you want to
be greater than or equal to. You can also supply a Column Name instead of
the number 17, if you want to compare to values in the same row, but you'll
need to add error handling or a method to insure that the second column
contains a value.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top