Is there a known bug with using both text AND numbers in an OR statement?

[#1]

I've come across a weird scenario and I'm wondering if anyone else has seen this or has worked around it.

In a WHERE clause I can successfully select a Sales Office that is all text with:

AND [Sales Office]='CAN'

I can also successfully select multiple Sales Offices with an OR statement:

AND ( [Sales Office] = '010' OR [Sales Office] = '021' OR [Sales Office] = '041')

When I add the textual Sales Office to the numerical ones as part of my OR statement, I only get numerical results coming through, thus:

AND ( [Sales Office] = '010' OR [Sales Office] = '021' OR [Sales Office] = '041' OR [Sales Office]='CAN')

Only gives me results back that are for '010' '021' or '041' - I do not get any data out of the QVD that I'm reading that pertains to Sales Office 'CAN'

Does anyone know if this is a bug or see any glaring mistakes in my clause?

[Miguel_Angel_Baeyens]

Hello,

I'd use the Match() function instead, that will take the value in the field and return greter than zero if any of the followgin values match exactly (case sensitive)

WHERE MATCH([Sales Office], '010', '021', '041', 'CAN') > 0;

Hope that helps.

[#3]

Unfortunately this did not perform any differently. I still only show the numbered sales offices in the result.

Any other ideas?

[#4]

Your numbers ARE TEXT because they are quoted. I think you have another issue.

[Anonymous]

I don't see any mistakes, and expect this to work.
One note though - it is case-sensitive, so the 'CAN' text must be exactly all cap in oyur source data. Or, you can try
... OR upper([Sales Office])='CAN'