Microsoft Access Fails when Exporting XML using the Sql LIKE condition versions 2013 & 2007.

Question

Using both Access 2007 and 2013, I find what appears to be a bug when exporting sql queries to XML when the LIKE condition is used. 

Example:

SELECT db.field1, Count(db.field2) as Count

FROM db WHERE db.field3 Like 'Yes,*'

GROUP BY db.field1

HAVING db.field1 in (Value1, Value2, Value3; Value4); 

Query runs fine and shows counts for all values that exist with a 'Yes,' in field3 of the table (as shown in Access). 

Also, query will export to txt, xslx, pdf, etc. (anything other than XML) are ok, and show all values and counts.

Exporting to XML, however, creates a file with the header and creates XSD, but doesn't have the data present.  Anything below the XML header is missing.

Removing the Like condition makes the export to XML (same query) works fine.  Add the Like condition back, it fails.

Worst yet, if I have a more complex query using the OR command, and if my conditions on one side of the OR  do not have the Like command, data appears in the XML, but only that data on the side of the OR command that doesn't use the Like command. 

This gives the impression that the query is working fine but the data is incorrect (counts not inclusive of the Like condition). 

Have had to make work arounds using additional fields as flags and using record updates with Like command to fill these fields with flag data that I can query on. 

Hope there is a solution since this approach adds significant data management steps. 

Thanks.

Answer 1

Thanks, This works, I set the database to ANSI92 and will use the appropriate wild card (%).

Answer 2

This is known issue (bug), look at below article from Microsoft, and the workaround is also available in this article:

http://support2.microsoft.com/kb/823224

Hope this helps,

Daniel