Formatting a date

[NISMOJim]

My query is pulling together all of the information I need, now I just need to sort it & show the date format I want. The user selects a fiscal year through a form to let the query know which date range to display.
The dates span across a fiscal year, starting with 4/09 (if 2009 is entered for example), ending with 3/10. Of course when I try sorting the Date ascending, the query shows 1/10, 2/10, 3/10, 4/09, 5/09...12/09. I tried adding another column to the query with a "yy/mm" format to sort by the year. This column then displays the dates as 10/1, 10/2, 10/3, 10/4, 10/5...10/12. I don't understand why it changed the year for all records as "10".
Ultimately, I would like the query to show the dates as Apr 09, May 09, Jun 09...Mar 10. This format & order is important, because it is being exported into Excel as a chart with a data table. Can someone please explain what I am missing?
Thank you in advance for all help.

[HiTechCoach]

Would you please post the SQL for your query.

[NISMOJim]

Here it is. The parameters to enter as an example would be Start: 4/1/2009 End: 3/31/2010.

SELECT AnnActInv2.[Date By Month], AnnActInv2.[Last Of UnTtl] AS [Unleaded Available], AnnActInv2.[Last Of PrTtl] AS [Premium Available], AnnActInv2.[Last Of OUTtl] AS [Oxy Unleaded Available], AnnActInv2.[Last Of OPTtl] AS [Oxy Premium Available], AnnActInv2.[Last Of DlTtl] AS [Diesel Available], AnnActInv2.[Last Of SaTtl] AS [Salvage Available], AnnActDel.[Sum Of InvcUn] AS [Unleaded Delivered], AnnActDel.[Sum Of InvcPr] AS [Premium Delivered], AnnActDel.[Sum Of InvcOxU] AS [Oxy Unleaded Delivered], AnnActDel.[Sum Of InvcOxP] AS [Oxy Premium Delivered], AnnActDel.[Sum Of InvcDl] AS [Diesel Delivered], AnnActPrc.[Sum Of ChgAmt] AS [Delivery Cost], AnnActUse2.[Sum Of Quantity] AS [Fuel Used (Gallons)]
FROM ((AnnActInv2 LEFT JOIN AnnActDel ON AnnActInv2.[Date By Month] = AnnActDel.[DelDate By Month]) LEFT JOIN AnnActPrc ON AnnActInv2.[Date By Month] = AnnActPrc.[DelDate By Month]) LEFT JOIN AnnActUse2 ON AnnActInv2.[Date By Month] = AnnActUse2.Date;

The date fields in the tables are all set to Date/Time, and the queries properties are set as Medium Date.

Thanks for your help.

[ajetrumpet]

I can offer you some insight here as well Jim...

Of course when I try sorting the Date ascending, the query shows 1/10, 2/10, 3/10, 4/09, 5/09...12/09. I tried adding another column to the query with a "yy/mm" format to sort by the year. This column then displays the dates as 10/1, 10/2, 10/3, 10/4, 10/5...10/12.

Read about the FORMAT() function in Access's help file. You can learn all you need to know about doing this sort of thing in that file.

for example, if you want to sort 4/1/2009 - 5/1/2010 in ASC order, first by month name ABBR and then by the year as a two digit number, this will be fine:

ORDER BY FORMAT([datefield], "mmm") & " " & FORMAT([datefield], "yy")

Also, if you look at the help file for example, sorting the actual month numbers is not a problem if you use the "mm" input. It says right there that it returns leading 0s for 1-digits outputs, which solves the issue of a sort order that comes out like:

1
11
12
2
3
4
etc...

HTH

[weekend00]

Why didn't you just order by the datefield and show the date as format(datefield,"mmm yy")?

[NISMOJim]

Weekend00, I tried that, and the order displays Apr09, Aug 09, Dec 09, Jan 10, Jul 09...
I've tried all of the formatting that ajetrumpet mentioned too, but it still isn't doing what it is supposed to.
I have come up with a workaround that I will be trying today. Once everything is exported to the Excel file, I will make another worksheet that copies the one with the exported data (since it is overwritten each time, I can't format that one). Once the cells in the new sheet are set to equal the cells in the exported sheet, I can format them & base the chart off the second sheet.
Thank you for your suggestions.

[weekend00]

I meant you order by the datefield( date/time field in the table), not the showing text field (Apr 09...)

[NISMOJim]

Thanks for the advice, I'll give it a try. In the mean time, I tried the Excel thing yesterday, but the date still wouldn't format the way I wanted it to. I went back to the db and changed the format from mmm/yy to mmm/yyyy and everything started working fine. Maybe with the month & year both originally in 2 digit format, the program didn't know what to do with them when I tried to change it around. That project is now finished & working as planned, time to work my way to the next obstacle.
Thanks for all of your input on this problem.