MS Access Case sensitive query giving incorrect result

user_192510 0
Viewed 50

Why do these queries give different results? Reference is a single character column and I would expect to have a result giving counts for upper and lower case letter 'r'.

Select SUM(IIF(StrComp([REFERENCE],'R',0) = 0, 1, 0)) AS BIG_R,
       SUM(IIF(StrComp([REFERENCE],'r',0) = 0, 1, 0)) AS LITTLE_R
From [SYMREF]
Where [PROGRAM] = 'SOMEPROGRAM'

The result is that both BIG_R and LITTLE_R are the same and equal the count of BIG_R's

However,

Select SUM(IIF(StrComp([REFERENCE],'r',0) = 0, 1, 0)) AS LITTE_R,
       SUM(IIF(StrComp([REFERENCE],'R',0) = 0, 1, 0)) AS BIG_R
From [SYMREF]
Where [PROGRAM] = 'SOMEPROGRAM'

Again LITTLE_R and BIG_R are the same, but this time they equal the count of LITTLE_R's

This looks like a bug in the way MS Access processes this type of query, or have I missed something here?

ms-access
1 Answers

Access (or probably rather JetEngine) thinks that StrComp is called twice with the same argument and optimizes away one of the two calls.

A workaround is to compare the ASCII character values (Asc("r") = 114, Asc("R") = 82):

Select 
    SUM(IIF(Asc([REFERENCE]) = Asc('R'), 1, 0)) AS BIG_R,
    SUM(IIF(Asc([REFERENCE]) = Asc('r'), 1, 0)) AS LITTLE_R
From [SYMREF]
Where [PROGRAM] = 'SOMEPROGRAM'

Yet another workaround:

Select SUM(IIF(StrComp([REFERENCE],Chr$(82),0) = 0, 1, 0)) AS BIG_R,
       SUM(IIF(StrComp([REFERENCE],Chr$(114),0) = 0, 1, 0)) AS LITTLE_R
From [SYMREF]
Where [PROGRAM] = 'SOMEPROGRAM'

Here the two inputs to StrComp are clearly different. So, the second call not optimized away.

user_8809900
Related