query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sandrao - 19 Nov 2007 18:25 GMT

I Am having trouble with a Query. I want to use an "orderdate" in a combobox
to select dates for reports.
Here is what the query looks like: "SELECT DISTINCT
tblNewTransfer.[OrderDate] FROM tblNewTransfer ORDER BY
tblNewTransfer.OrderDate;". The problem is that it is not selecting distinct
dates it select all the dates which lists numerous duplicates. How can I
insure that the cbo will select only each different date with no duplicates?

Reply to this Message

Jason Lepack - 19 Nov 2007 18:29 GMT

They are distinct. They include times as well as dates though.

> I Am having trouble with a Query. I want to use an "orderdate" in a combobox
> to select dates for reports.
[quoted text clipped - 3 lines]
> dates it select all the dates which lists numerous duplicates. How can I
> insure that the cbo will select only each different date with no duplicates?

Reply to this Message

KARL DEWEY - 19 Nov 2007 21:00 GMT

Try this ---
SELECT DISTINCT DateValue(tblNewTransfer.[OrderDate]) FROM tblNewTransfer
ORDER BY DateValue(tblNewTransfer.OrderDate);

Signature

KARL DEWEY
Build a little - Test a little

> They are distinct. They include times as well as dates though.
>
[quoted text clipped - 5 lines]
> > dates it select all the dates which lists numerous duplicates. How can I
> > insure that the cbo will select only each different date with no duplicates?

Reply to this Message

John W. Vinson - 19 Nov 2007 20:59 GMT

>I Am having trouble with a Query. I want to use an "orderdate" in a combobox
>to select dates for reports.
[quoted text clipped - 3 lines]
>dates it select all the dates which lists numerous duplicates. How can I
>insure that the cbo will select only each different date with no duplicates?

As Jason says, the problem is that your OrderDate field probably has values
like #11/17/2007 11:24:31am# and #11/17/2007 02:16:28pm# - which ARE distinct
values, though they will look the same if you format the field to show the
date portion only.

Try

SELECT DISTINCT DateValue(tblNewTransfer.[OrderDate]) FROM tblNewTransfer
ORDER BY DateValue(tblNewTransfer.OrderDate);

to truncate it to just the date portion.

John W. Vinson [MVP]

Reply to this Message