EGuide Query Summary Function Gotcha

[Doc_Duke]

Just to share a nasty surprise. I was using Query Builder to count the number of visits that had dates present The generated code was

(SUM(t1.SGVisitDate>.)) AS CHEvisitDone

When that code runs against a SAS dataset, all is well. When it runs against an Access table via a LIBNAME (to ACCESS, not to ODBC), it returns all NULLS (no errors or warnings).

If I change the summary function to
COUNT(t1.SGVisitDate) AS CHEvisitDone
I get the expected answer with either source.

Caveat emptor! I will also send to tech support.

[Doc_Duke]

Response from designers to tech support: "It's not a bug, it's a feature."

I finally found the behavior documented on page 31-32 of the "SAS/Access 9.2 for Relational Databases, Reference" 4th Ed.

Caveat emptor.