Using LIKE in Count(IIF()) in MS Access, get NA count

Question

I'm attempting to get a count of NAs in a field in a table in MS Access. I have multiple NAs:

1. #N/A* (i.e. NAs that start with hash & "N/A" & some more text that varies.)
2. NaN
3. NA
4. N/A

(They all mean something different for us, so it i actually useful to have the different NAs.)

This works for one specific NA, "#N/A N/A":

SELECT Count(IIf([Short Int % Eqy Flt] = '#N/A N/A' ,0 )) AS MyTotal
FROM tblFirst250

The above returns 141, which seems correct.

This returns 0:

SELECT Count(IIf([Short Int % Eqy Flt] LIKE '#N/A' ,0 )) AS MyTotal
FROM tblFirst250

The above should return 142 because I have 141 "#N/A N/A" and 1 "#N/A Invalid Security".

Really what I am after is something that counts all of my NAs in the [Short Int % Eqy Flt] field. See my list of NAs above.

Answer 1

# has a special meaning [Any single digit (0–9)] when used in a Like pattern. You can tell Access to treat it as just the normal # character by putting it inside a character range like this ... [#].

Also, I think you want to match [#]N/A followed by additional characters, so tack on a wildcard character to the end of the pattern.

SELECT Sum(IIf([Short Int % Eqy Flt] LIKE '[#]N/A*', 1, 0)) AS MyTotal
FROM tblFirst250;

I used Sum instead of Count. But if you prefer Count ...

SELECT Count(IIf([Short Int % Eqy Flt] LIKE '[#]N/A*', 1, Null)) AS MyTotal
FROM tblFirst250;