Access vs Jet?

[Zybron]

Here's an interesting problem. I have the following query stored in a database that I'm working on called NotPaid17Days:

SELECT A1.CurrentPrice, Max(I1.PartNum) AS PartNumber, I1.ShortDescription AS Title, DateValue([A1.EndDate]) AS EndDate
FROM (Items AS I1 INNER JOIN Auctions AS A1 ON I1.ItemID = A1.ItemID) INNER JOIN Sales AS S1 ON A1.SaleID = S1.SaleID
WHERE (((DateDiff(&quot;d&quot;,[A1.EndDate],Now()))>17) AND ((S1.DatePaymentReceived) Is Null) AND ((S1.DatePaymentCleared) Is Null) AND ((S1.StatusID)<200) AND ((A1.StatusID)=80) AND (Year([EndDate])>2002))
GROUP BY A1.CurrentPrice, I1.ShortDescription, DateValue([A1.EndDate])
HAVING (((Max(I1.PartNum)) Is Not Null And (Max(I1.PartNum))<>&quot;&quot; And (Max(I1.PartNum))<>&quot; &quot)
ORDER BY DateValue([A1.EndDate]) DESC;

This query produces about 500 records from the data that exists in the database.

However, when I attempt to process this query from a visual basic application using Jet as the connection provider, I get an empty recordset. I've tried every method I know of to refer to this query from stored procedures to specifying the query in code and every time it produces an empty recordset. I can give some examples of the code I've used, but I've tried specifying the command object explicitly with the sql above as the command text, I've used the view collection (for some reason that was where it was, instead of the procedures collection) of a catalog object. Every method of processing queries through a Jet Provider that I could find on MSDN returned an empty recordset.

Is there something I'm missing here?

 

[JeremyNYC]

Well, we can't critique what we can't see. Post some code.

I know you've tried a bunch of different ways, but post the code as you would like to use it in your database, and tell us what you're expecting will happen and what does happen. We'll bwe able to work with you from there.

When you run a query through the qbe interface, by the way, you are using Jet to pull you data. Doing it through code is a different interface, but they both use Jet to get the data from your tables.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.