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