IIf Statement

[Pvarga]

I have 2 col and one or both may have date in it. If there is data in B I want to use that but if it is empty I will use A.

I have a statement that is doing what I want for B but if B is Null and I want A all I get is Error#.

What I have to do is count how many of each level and if they have a level in B I want that, if not use A.


Thanks

 

[shaggy]

You can create a new column.

expr1: iif([DATEFIELD1] is null,[DATEFIELD2],[DATEFIELD1])

- Kevin

 

[Pvarga]

Thank you, that does work. Now I want to count how many of each. I am able to get a count of all levels but where it is a 0 or blank I want to also have a count. I have tried, Is Null and <10 (because my lowest level is 10) and it does not work.

I would appreciate any suggestions.

Thanks

 

[shaggy]

I'm not sure if this is the easiest, but it works.

You can create new columns.

expr2: iif([DATEFIELD1] is null,1,0)
expr3: iif([DATEFIELD2] is null,1,0)

These columns will have a 1 in them if the [DATEFIELD] is null, or a 0 if not.

You can then count the 1s and that will give you a count of the nulls.

I don't know how to do the counts in one query. So far I've only been able to do them separately.