Why Does Access Return Different ResultSet When Query Run Programmatically?

Question

I've written a query in MS Access. This is a simplified version:

SELECT IIf([category] LIKE "*abc*","DEF",category) AS category
, Month
, Sum(qty) AS [qty] 
FROM [tableX] 
GROUP BY category, Month

The purpose of the query is to sum quantities of a product in different categories for different months. I want to aggregate categories like abc into a single category called ABC. When I view the query in Access the categories are correctly aggregated, but if I select from the query in C# code no aggregation is done.
Any ideas why this is this happening?

Answer 1

The wildcard for when using the Access database engine's ANSI-92 Query Mode is %, not *.

The Access database engine's OLE DB providers (e.g. via ADO classic, ADO.NET, etc) always use ANSI-92 Query Mode.

The Access UI uses ANSI-89 Query Mode by default but can be put into ANSI-92 Query Mode.

DAO always uses ANSI-89 Query Mode.

Using the (unsupported) ALIKE keyword always uses the '%' wildcard regardless of Query Mode.

Answer 2

if it's working in Access so try to make it as a Query in Access and use it from your APP.