Topics Search

Access Queries: Format Date by Year

Access Queries: Format Date by Year
Views: 4
Here is how my date is formatted:

Begin Date: (Format([courseBeginDate],"mm/dd/y")) AS [Begin Date]

I told it to Group By and Sort Descending

The Dates are sorted by the month then day.

I would like the date to instead sort by latest year, then month, then day.

For Example

Instead of looking like this
Sponsored Links:

More topics


Formatting date

I have a field in a table that inputs the date of birth in the following format - 01-Jan-2000. I am creating a report and would like to sort by year of birth. I have the report sorted on the date of birth field and have enter Year as Group on.

The date of birth field I have changed the input mask to 0000;;_ | I have tried short, medium and long date in the format properties.

The report is grouping correct by birth year but displaying the full date of one of the birthdates in the group. I only want it to display the year - ie 2000.

fiscal year format

I work for a company that has a fiscal year calendar like the public schools.
I would like to have my forms in Access go on a fiscal year calendar. Is there any way that access will let me change the date format so that November of 2003 is recognized as the new fiscal year of

Get fiscal year to show in a report

I have a database that I'm trying to get a report to sort on my fiscal year. I have a module that I set up (below) to set the dates for my fiscal year.


I then add the "Between FiscalYear() And Date()" to the date fields in my queries. the queries are working great. My issue is that I'm trying to get my reports to give the year-to-date based on the fiscal year but they keep breaking them down to the calendar year. How do I set the sort fields in the report to sort by my fiscal year of Nov 1 to Oct 31?

Date format to Month/Year only

I use to use this =Format(Date()-28,"m y" to show the previous month & year in a text box for several Reports I was running in Access 2000. I just updated to Access 2010 and this function no longer works.

How can get Date() to only show up as the Month & Year in a text box so I do not have change this every month manually?

Different format of dates

Should I maintain dates in Julian date mode (yd format), where y is the year and d is the day of the year (1-365 or 366 in leap years). Some systems people prefer the standard mm/dd/y format. What is the advantage n disadvantage of using Julian date

Financial Year not Calendar Year

I have queries where the criteria on a date field is =Year(Now()) And Month(Now()-1).

However, I've just been told that all reporting needs to be based on the Financial year (July to June) instead of the calendar year.

without hardcoding dates in my queries. and then call that function from the query? If so, how would this look?

Or, can I create a table with dates, and call that? (At least then it would only need to be updated in one place).

number to Date format

I have a number field which has date information in it. ex - 20110112 YYYYMMDD in that format. Don't ask why they didn't just make it a date format.

Right now a date conversion table is used. So when referencing that field it does a join on the number date and return the date format. Two field table. First column is 'number date format' and the second is 'return date format'. The table is manually kept up each year.


Just trying to figure out how to format that into a Date format so that I can perform date function on it using sql so that I don't have to maintain the date conversion table

Formatting a date

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

Date Picker

I'm using access 2007. All of my dates are bound, no inputmask, and the format is set to short date. Some of the dates have a smart tag linked to my outlook calendar. However when I select a date on the picker it changes from day-month-year to month-day-year depending on the date.

If the day is 1-12 it displays as day-month-year
If the day is higher than 12 it displays as month-day-year

I don't care which way the date is displayed, it just can't be both ways! Is there a way to fix this so that it is always displayed one way

Cunvert date

out. I want a query(Update) which makes a date

like 14-May-08(Format - Date - Medium) to May'08(any format).

Please solved quickly as I have a database from 01-Jan-2003 to till date. & I have to calculate per month productivity. so I need extract month & year in this format(May'08) form a date like (14-May-08).