Topics Search

Access Queries: Format Date by Year

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


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.

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

Open multiple queries, change date range, run and close

Open multiple queries, change date range, run and close Icon
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 - Date Range (Financial Year) problems

Query - Date Range (Financial Year) problems Icon
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

Current and Prior Fiscal Year to Date Icon
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.

Query for a year of data

Query for a year of data Icon
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])

Would like date year to be entered as 12 or 2012 to return 2012 always

Would like date year to be entered as 12 or 2012 to return 2012 always Icon
I have a date field that is a short date format, have Date() as default value, and the input mask as 99/99/9900;0;" " so theoretically I could enter "7/27/12" OR "7/27/2012" and both should come out as 7/27/2012, but if you enter 2 digits for the year, it won't allow.

It MUST have all 4 digits of the year be typed in. I would like the ability to do both.