Topics Search

Access Queries: Format Date by Year

Access Queries: Format Date by Year
Views: 5
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:

Similar posts...


fiscal year format

fiscal year format Icon
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

Formatting date

Formatting date Icon
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.

Date Picker change format

Date Picker change format Icon
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

Get fiscal year to show in a report

Get fiscal year to show in a report Icon
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

Date format to Month/Year only Icon
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

Different format of dates Icon
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

Financial Year not Calendar Year Icon
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

number to Date format Icon
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

Formatting a date Icon
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

Dates from xcel to Access

Dates from xcel to Access Icon
Having spent 2 days trying to solve this problem I am at my wits end and appreciate some help from an expert.

Here is my problem;

I am trying to transfer some date type data from xcel to an existing table in access but having a problem with the format.

Format in Access in my date field = number (long integer) and the way the date is currently displayed in the existing table is for example 1 Jan, 4 may etc. (i.e. no year).
Please do not ask me why it is in this format as I never designed the orignal database.

The data I wish to append as new records is from an excel spreadsheet shows the date in a similiar visual format of 4 may etc and I changed the excel format to number prior to importing to my temp Access table and on import it changes it to text and when I try to change back to number is deletes all my dates in this column