problems with ordering by date

  • Thread starter Thread starter aa
  • Start date Start date
A

aa

My ORDER BY field_date does not work properly:
date
05/02/2004
12/02/2004
12/02/2004
12/02/2004
29/09/2003
29/09/2003
31/07/2003
31/07/2003
31/10/2003
31/10/2003



It lookd like it mistakes day for month.
Indeed in the query I have to enter date as #mmddyyyy# dispite in Control Panel I have UK input locale and the date format is indicated there as ddmmyyyy

How do I sort this out?
 
aa

It looks like your sorting this date as a text (is your field's datatype a Text or a Date/Time?). Where does 01/01/2002 fit in your sorting? If it is the first item, your data is text and you need to change it to Date/Time. You can set the Format in the table's design view to dd/mm/yyyy. This will also allow you to enter your date as 01/22/2004 and get 22/01/2004

If all else fails, use ORDER BY YEAR([field]),MONTH([field]),DAY([field])

Hope this helps

Ji
 
1. Date/Time
2. In table and in queries date shows as ddmmyyyy
3. ORDER BY YEAR([field]),MONTH([field]),DAY([field]) returns an error
saying that these fields in ORDER BY has not been selected in the query.
How should I use them? I replaced the [field] with the name of the field
containing dates


Jim Richards said:
aa,

It looks like your sorting this date as a text (is your field's datatype a
Click to expand...
Text or a Date/Time?). Where does 01/01/2002 fit in your sorting? If it is
the first item, your data is text and you need to change it to Date/Time.
You can set the Format in the table's design view to dd/mm/yyyy. This will
also allow you to enter your date as 01/22/2004 and get 22/01/2004.
If all else fails, use ORDER BY YEAR([field]),MONTH([field]),DAY([field]).

Hope this helps,

Jim
Click to expand...
 
It looks to me that Access is sorting them as Text rather than Date.

Is the Field actually a Date Field or a Text Field that looks like date?

Do you use any function on this? i.e. Format([DateValue], "dd/mm/yyyy") actually returns a (Variant of) String / Text type.

--
HTH
Van T. Dinh
MVP (Access)


My ORDER BY field_date does not work properly:
date
05/02/2004
12/02/2004
12/02/2004
12/02/2004
29/09/2003
29/09/2003
31/07/2003
31/07/2003
31/10/2003
31/10/2003



It lookd like it mistakes day for month.
Indeed in the query I have to enter date as #mmddyyyy# dispite in Control Panel I have UK input locale and the date format is indicated there as ddmmyyyy

How do I sort this out?
 
(1) Please do not post HTML! Many people object to receiving HTML posts & will not reply to them.

(2) Show us the SQL. Clearly it is not just "SELECT field_date ORDER BY field_date" - otherwise the column heading would be "field_date".

(3) Dates provided between #..# delimiters must always be specified in month/day/year format, regardless of your PC's locale settings.

HTH,
TC

My ORDER BY field_date does not work properly:
date
05/02/2004
12/02/2004
12/02/2004
12/02/2004
29/09/2003
29/09/2003
31/07/2003
31/07/2003
31/10/2003
31/10/2003



It lookd like it mistakes day for month.
Indeed in the query I have to enter date as #mmddyyyy# dispite in Control Panel I have UK input locale and the date format is indicated there as ddmmyyyy

How do I sort this out?
 
My ORDER BY field_date does not work properly:
date
05/02/2004
12/02/2004
12/02/2004
12/02/2004
29/09/2003
29/09/2003
31/07/2003
31/07/2003
31/10/2003
31/10/2003



It lookd like it mistakes day for month.
Indeed in the query I have to enter date as #mmddyyyy# dispite in Control
Click to expand...
Panel I have UK input locale and
the date format is indicated there as ddmmyyyy
Click to expand...

Are you saying that Regional Control Panel is set to "ddmmyyyy"?

I experimented, and went into my Regional Control Panel, and changed the
date format ddmmyyyy, and Access immediately began displaying the dates that
way.
In Design View for the table (I picked a random table to look at, one with
a Date/Time column), the Format Drop-Down box for the Date/Time Column only
offered formats using the new date display set-up, which seems to be
controlled by the Regional Control Panel.
Typing in the following string in the Format Drop-Down box "mm/dd/yy
mm:hh:ss" made Access start displaying things correctly once more.
(And yes, afterward, I reset my Regional Control Panel to normal US
format.)


Sincerely,

Chris O.
 
It is Date/Time field.
No function is applied to it
If it is relevant the whole table was exported from Progress into Excell and then from Excell imported to Access
It looks to me that Access is sorting them as Text rather than Date.

Is the Field actually a Date Field or a Text Field that looks like date?

Do you use any function on this? i.e. Format([DateValue], "dd/mm/yyyy") actually returns a (Variant of) String / Text type.

--
HTH
Van T. Dinh
MVP (Access)


My ORDER BY field_date does not work properly:
date
05/02/2004
12/02/2004
12/02/2004
12/02/2004
29/09/2003
29/09/2003
31/07/2003
31/07/2003
31/10/2003
31/10/2003



It lookd like it mistakes day for month.
Indeed in the query I have to enter date as #mmddyyyy# dispite in Control Panel I have UK input locale and the date format is indicated there as ddmmyyyy

How do I sort this out?
 
SELECT Customercode, Invoiceno As Invoice, Invdate, Sum([Salevalue]) AS InvoiceTotal
FROM sales
WHERE Invdate>#07/01/2003# AND Invdate<#04/01/2004# AND Customercode="4MULT01" AND Salevalue>0
GROUP BY Invdate, Customercode, Invoiceno
UNION SELECT " " as Customercode, " " As Invoice, "Grand Total" as Invdate, Sum([Salevalue]) AS InvoiceTotal
FROM sales
WHERE Invdate>#07/01/2003# AND Invdate<#04/01/2004# AND Customercode="4MULT01" AND Salevalue>0
ORDER BY Invdate;
(1) Please do not post HTML! Many people object to receiving HTML posts & will not reply to them.

(2) Show us the SQL. Clearly it is not just "SELECT field_date ORDER BY field_date" - otherwise the column heading would be "field_date".

(3) Dates provided between #..# delimiters must always be specified in month/day/year format, regardless of your PC's locale settings.

HTH,
TC

My ORDER BY field_date does not work properly:
date
05/02/2004
12/02/2004
12/02/2004
12/02/2004
29/09/2003
29/09/2003
31/07/2003
31/07/2003
31/10/2003
31/10/2003



It lookd like it mistakes day for month.
Indeed in the query I have to enter date as #mmddyyyy# dispite in Control Panel I have UK input locale and the date format is indicated there as ddmmyyyy

How do I sort this out?
 
Open the imported Table in DesignView and check the data type of the Field
and check the Field type. I think it is NOT a DateTime as you expected.

If it were a DateTime Field, Access/JET would normally sort correctly.

--
HTH
Van T. Dinh
MVP (Access)


It is Date/Time field.
No function is applied to it
If it is relevant the whole table was exported from Progress into Excell and
then from Excell imported to Access
 
AHA! The UNION Query is causing the problem. It is forcing InvDate to be a
text field since you are setting InvDate to "Grand Total" in the second query in
the Union.

One way You can solve this by adding fields to your queries.


SELECT Customercode, Invoiceno As Invoice, Invdate,
Sum([Salevalue]) AS InvoiceTotal,
InvDate as RecordType
FROM sales
WHERE Invdate>#07/01/2003# AND Invdate<#04/01/2004# AND Customercode="4MULT01"
AND Salevalue>0
GROUP BY Invdate, Customercode, Invoiceno, "A"
UNION SELECT " " , " " , "Grand Total" , Sum([Salevalue]), #1/1/9999#
FROM sales
WHERE Invdate>#07/01/2003# AND Invdate<#04/01/2004# AND Customercode="4MULT01"
AND Salevalue>0
ORDER BY 5;
 
Back
Top