Covert english date format to fransh date .Use of Order BY in access 2000

[kapusta117]

HELLO
I'm working in Access database .. i'm using this SQL query to extract dates in English format below :
SELECT DISTINCT Format([Date Commande],"yyyy/mm") AS Expr1
FROM Relevé
GROUP BY Format([Date Commande],"yyyy/mm");
Result:
Expr1
2006/10
2006/11
2008/10
2008/11
2008/12
2009/01
AS YOU see i have the good order of my dates ..But when i m trying to have the same result (Order)
in france format date ... my query doesnt work properly :
SELECT DISTINCT Format([Date Commande],"mm/yyyy") AS Expr1
FROM Relevé
GROUP BY Format([Date Commande],"mm/yyyy");
Result is :
Expr1
01/2009
10/2006
10/2008
11/2006
11/2008
12/2008

AS you see the order of date is not respected..
My question is how to convert date from english format to frensh format...IS THERE ANY function or script to do this???.
Im beginner in ACCESS..So i m pleased to get a response.
THANK YOU.

 

[namliam]

This is because FORMAT returns a character and not a date...
i.e. sorts a,b,c,0,1,2,3,

This screws up your sorting...

You should sort by a proper date... Something like:

SELECT Format([YourDate],"MM-YYYY") AS Expr1
FROM YourTable
GROUP BY Format([YourDate],"MM-YYYY"), CDate(Format([YourDate],"YYYY-MM-" & "01"))
ORDER BY CDate(Format([YourDate],"YYYY-MM-" & "01"));

Or use the 'UK" format as you call it or ISO as I would call it to format it... simply dropping the CDate function from above SQL.

 

[Guus2005]

IMHO:
Don't convert a date format into any another date format, use long instead:
Access stores a date as a long (datetime as a double). If you do the same, you 'll never have to figure out which date was 1-2-2008 again, Feb 1st or Jan 2nd. It will always be 39479.
Using cdate(39479) it gives you 1-2-2008 again.

Sorting the query will never fail:

SELECT DISTINCT Format([YourDate],"MM-YYYY") AS Expr1
FROM Relevé
GROUP BY Format([YourDate],"MM-YYYY") AS Expr1
ORDER BY clng([Date Commande]);

Enjoy!

 

[namliam]

Sorting the query will never fail:

SELECT DISTINCT Format([YourDate],"MM-YYYY") AS Expr1
FROM Relevé
GROUP BY Format([YourDate],"MM-YYYY") AS Expr1
ORDER BY clng([Date Commande]);

You cannot order by this ... it isnt in the group by therefor it will return an error.
Furthermore you are ordering by individual dates, instead of months which the OP is outputting... again not possible.

If you want to show MM-YYYY or DD-MM-YYYY you HAVE to sort by either a REAL date or the ISO (YYYYMMDD) format to get a proper sort.
There is unfortunatly no other way to do it

 

[kapusta117]

Hello

THANK YOU for response...
I have tried the two requery you give me but i have error syntaxe in the first and conflict between distinct and lng ( in the second ...

I tried this query ...
IT WORKS ... GREATLY

SELECT Format([Date Commande],"mm/yyyy") AS Expr1
FROM Relevé
GROUP BY Format([Date Commande],"mm/yyyy"),
Format([Date Commande],"yyyy/mm")
ORDER BY Format([Date Commande],"yyyy/mm");




SEE YOU SOON