Date query not returning distinct dates

[okiiyama]

Here is my query, but it still returns multiple instances of the same date...

SELECT DISTINCT PurchaseOrderTBL.PODate
FROM PurchaseOrderTBL
WHERE (PurchaseOrderTBL.PODate Is Not Null)
GROUP BY PurchaseOrderTBL.PODate
ORDER BY PurchaseOrderTBL.PODate DESC;

Does anyone know what I can do to fix this, or what is wrong?

Thanks.

 

[GingerR]

hmmmm...it worked perfectly for me. Are you sure you're seeing the same dates?

 

[okiiyama]

Here is my return:

PODate
10/19/2004
10/19/2004
10/18/2004
10/18/2004
10/15/2004
10/15/2004
10/15/2004
10/14/2004
10/13/2004
...
...

Yeah.. Im a little confused as well.

 

[Golom]

It could be that your dates are in fact date and time so "10/19/2004" is really "10/19/2004 10:14:52 AM", etc. Try something like

SELECT DISTINCT cDate(Int(PurchaseOrderTBL.PODate))
FROM PurchaseOrderTBL
WHERE (PurchaseOrderTBL.PODate Is Not Null)
ORDER BY Int(PurchaseOrderTBL.PODate) DESC;

You don't need both "DISTINCT" and "GROUP BY" because both produce a single value for a date.

 

[PHV]

You may also try this:
SELECT DISTINCT Format(PODate,'mm/dd/yyyy') AS PO_Date
FROM PurchaseOrderTBL
WHERE PODate Is Not Null
ORDER BY PODate DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244