Access Queries: Format Date by Year

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:

Related 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?

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

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?

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

Validation Rule for specific answer
I have a rule to limit the response a user can input on the form.
The code works in terms of not allowing a date prior to current year, or does not allow it to exceed current year + 20. But if a user accidently reponds then deletes and trys to move on, it states a 94 error: Null value debug.

Private Sub Entrance_Doors___Flats__Renew_Year__20_LE_AfterUpdate() If Me.[Entrance Doors - Flats (Renew Year) 20 LE] < Format(Date, "yyyy") Then
MsgBox "Renew Year Invalid: Less than current year!"
End If If CLng(Me.[Entrance Doors - Flats (Renew Year) 20 LE] >= (Format(Date, "yyyy") + 20)) Then
MsgBox "Renew Year Invalid: Exceeds life expectancy

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).

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

Financial Year Format
I have a form that allows input of historical finance data. I want to give the user a field where (s)he can input a financial year (eg 2009/10, 2007/08).

Do I do this as a date field, or a string field?

I want to allow a financial year as input - and hopefully run queries against the field (eg find the total of <FinanceItem1> for the five years of 2000/01 -&gt

number to Date format
Ex - 20110112 YMMDD 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.

Dates from xcel to Access
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

Query - Date Range (Financial Year) problems
I've recently been engaged to develop an Access DB for a church's community support program, and am running into some trouble designing a particular query.

I've a Date/Time field, "VisitDate" in tblVisits. Format is short date, Australian Time system (mm/dd/y).

What I'm trying to do is locate all records for the past Financial Year. Our financial year goes from 01 July (01/07/Y) to 31 June (31/06/Y) - This would be the "07/08 Financial Year"

Current and Prior Fiscal Year to Date
Our fiscal year runs from 10-1 through 9-30. The fiscal year is the year that the September falls in.I.e. Sept 2012 is FY 2012, Oct. 2012 is FY 2013.

invoicedate is m-dd-y format. I need to compare the current fiscal year-to-date with the prior fiscal year-to-date with the ending month being the month prior to the month the query is ran.(query run in Aug would show data through July, etc).

I can get this year's data by using iif.but having trouble getting last year's data.

Julian Date and Other
I am trying to design an Access DB where one of the field (control number) consists of the format:


Where all entries begin with V followed by the Julian date (2 digit year and three digit day) and a 3 digit 1 up number. The 3 digit 1 up number would revert back to 001 at the beginning of each year.

I have been able to get the Julian date to some degree using "yyy" vice general date in the format field proprieties. The problem here is when the day is less then 100 I only get a 2 digit place and would like 3 digits, ex. 025.

Can anyone provide a way of doing this? Unfortunately, I don't do coding

date/time field
Does it not really matter if a date in Access is defined as 'text' in the data type column, rather than date/time.

Because this way, I can get ColdFusion to output a list of members held in the database from a certain year, with just the date and not the time and in the correct format of dd/mm/y ratherthan y/mm/dd, which is what I was getting when I set joinDate to date/time.

Is it OK that I've used the text format rather than date/time, or will this produce errors somewhere along the line?

Open multiple queries, change date range, run and close
Using Access 2010, I have a number of queries used in sub-reports for my master report. At the beginning of the month, I need to open each query, change the date (it will be the same date range for all of the queries) criteria(s), run the query, close it and then move on to the next query and do the same thing.

Since I have over a 100 queries to accomplish the massive reporting involved, I really don't want to do this individually.

About have the queries use a "between" date range and the other half are grouped by month so they use a "month year" range. Most of the queries have multiplecriteria so the date criteria might be used up to 10 times in one query.

Query for a year of data
Not sure how to look this up, but I am trying to streamline a lookup I have made on a large amount of data.

The data has a date field that is stored in the usual way (dd/mm/y). In a query, and the query would return items with the year betweenJan 1 [year] and Dec 31 [year].

can you partially input a date criteria and sub in the date with a prompt?

I've tried a few things but I have not done something like this before. He is what I have tried to just get 1 month to work, but no success:

= 1/31/[YEAR]
= #1/31/[YEAR]#
= "1/31/"&[YEAR]

Also, would it take 1 input to be used 2 times if I used the same name in the []s?
(such as =between [YEAR] and [YEAR])