Select query return no records when exported (Excel or XML files)

[vinz]

Hi everyone. first post here.

I have a problem with certain select queries.
From within Access they work just fine (return correct records in datasheet view) but when data connected from Excel or exported as XML files they return no records.



When do they start failing?

Now, for instance, this works fine:

SELECT [Table 1].[Arg 1], [Table 1].[Arg 2], [Table 1].[Arg 3], [Table 1].[Arg 4], Sum([Table 1].Count) AS SumOfCount
FROM [Table 1]
GROUP BY [Table 1].[Arg 1], [Table 1].[Arg 2], [Table 1].[Arg 3], [Table 1].[Arg 4]
ORDER BY [Table 1].[Arg 3];

This, same but with records narrowed with inner join to same field from another table, returns no records at all:

SELECT [Table 1].[Arg 1], [Table 1].[Arg 2], [Table 1].[Arg 3], [Table 1].[Arg 4], Sum([Table 1].Count) AS SumOfCount
FROM [Table 1]
FROM [Table 1] INNER JOIN [Table 2] ON [Table 1].[Arg 2] = [Table 2].[Arg 2]
GROUP BY [Table 1].[Arg 1], [Table 1].[Arg 2], [Table 1].[Arg 3], [Table 1].[Arg 4]
ORDER BY [Table 1].[Arg 3];

Likewise, narrowing with criteria fails too:

SELECT [Table 1].[Arg 1], [Table 1].[Arg 2], [Table 1].[Arg 3], [Table 1].[Arg 4], Sum([Table 1].Count) AS SumOfCount
FROM [Table 1]
GROUP BY [Table 1].[Arg 1], [Table 1].[Arg 2], [Table 1].[Arg 3], [Table 1].[Arg 4]
HAVING ((([Table 1].[Arg 4]) Like "String"))
ORDER BY [Table 1].[Arg 3];

Now, what the hell is going on?
I think it is important to stress that everything works fine in datasheet view, and that when a query fails from Excel (Data connection to Access query) then 100% that query also generates an empty XML with size 1kb, and vice versa. This is reproduced with Access 2007 and 2010, Excel 2003, 2007 and 2010, and different PCs.

It must be something to do with exporting the queries.

Google has not been my friend with this

Any help would be muuuuuuuch appreciated.


Thanks!

Vinz

[vinz]

OK, solved.

This behavior occurs when you export to XML. XML uses ActiveX Data Objects (ADO) to query the data from the database. When you use a Jet-specific wildcard character in the Access query, the query returns no records. This problem occurs because ADO recognizes only American National Standards Institute (ANSI) 92 wildcard characters.

It fails to mention that it also applies when querying from Excel.

I shall use "%" instead of "*". Therefore %string% in place of *string*, works like a charm.

In my previous post the INNER JOIN example was with a subquery that used a * wildcard

Here's the link to the microsoft page ->Access queries with a wildcard character


Hope this is helpful for someone.