IIF statement

[darren]

I want an IFF statement that will do two functions.

1. where a parameter does not exist in table1 return 0 (zero).
2. However, if the parameter does exist on one or more occasions in Table1,
then sum the number of occurances of that parameter.

I have started the Iff stement as follows and returns a 0 (zero) where a
paramter does not exist in Table1
IIf("[Param]","[Table1]"=" ","0")

Any suggestions on how to finish this IFF statement to achieve point 2 above.

Thanks

 

[Dale Fye]

Actually, the IIF statement you have will not work in its current form.

I assume that you want to use this in some sort of query, or as the control
source for some control on a form. Why don't you give better description of
what you are trying to do, and someone will probably come up with a good idea
of how to address the problem.

My first inclination would be to use the DCOUNT for this. The purpose of
DCOUNT is to count the number of records in a table that match some criteria.
An example would be:

DCOUNT("ID", "Table1", "[Param] = ''")

This instance would count the number of records in Table1 where the [Param]
field contains an empty string. To count the number of records where [Param]
is null or blank, you could use:

DCOUNT("ID", "Table1", "LEN([Param] & '') = 0")