Change a Date Format (1 Viewer)

Tom d

Member
Local time
Today, 00:48
Joined
Jul 12, 2022
Messages
45
I have a query with a date field named 'Date_Entered By Month' in the format of 'mmyyyy' and the query is sorted by the date field. I want the format in the report to be "mmmmyyyy'. How do I write the format statement?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:48
Joined
Oct 29, 2018
Messages
19,201
Sounds like "mmmmyyyy" would work, have you tried it? What happened?
 

plog

Banishment Pending
Local time
Yesterday, 23:48
Joined
May 11, 2011
Messages
11,020
2 digits is all you need to represent all the months. What do you expect in your other two spots?

If you simply want 00 before every month, you would just add those:

Month year: "00" & Format (Date field, "mmyyyy")
 

MarkK

bit cruncher
Local time
Yesterday, 21:48
Joined
Mar 17, 2004
Messages
7,951
There are no queries in your database.
 

MarkK

bit cruncher
Local time
Yesterday, 21:48
Joined
Mar 17, 2004
Messages
7,951
SQL:
SELECT tlbExpenses.ID, tlbExpenses.Category, Format([Date_Entered],"mmmmyy") AS MyDate
FROM tlbExpenses;
This query works for me in your database.
 

GPGeorge

Grover Park George
Local time
Yesterday, 21:48
Joined
Nov 25, 2004
Messages
788
I have a query with a date field named 'Date_Entered By Month' in the format of 'mmyyyy' and the query is sorted by the date field. I want the format in the report to be "mmmmyyyy'. How do I write the format statement?
I'm going to ask a dumb question. Can you provide an example of a value formatted that way: "mmmmyyyy"? What do you expect to see? We can guess about it, but given that "mmmmyyyy" might mean a couple of different things, I'd like to know for sure what you expect to see.

This is what I would expect to see, for example:

1659309969329.png
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:48
Joined
Oct 29, 2018
Messages
19,201
Tried =Format([Date-Entered By Month],"mmmmyyyy")
Received error #Type!
This worked for me.

1659310168112.png


Here's the Design View:

1659310251489.png


Edit: Oops, too slow...
 

GPGeorge

Grover Park George
Local time
Yesterday, 21:48
Joined
Nov 25, 2004
Messages
788
It also occurs to me to wonder about the source value you are using? We all assumed it is a real date, merely formatted as "mmyyyy", but is it?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:48
Joined
Feb 19, 2013
Messages
14,740
Can you provide an example of a value formatted that way: "mmmmyyyy"?
?format(date(),"mmmmyyyy")
August2022
?format(date(),"mmmyyyy")
Aug2022
?format(date(),"mmyyyy")
082022
 

Tom d

Member
Local time
Today, 00:48
Joined
Jul 12, 2022
Messages
45
?format(date(),"mmmmyyyy")
August2022
?format(date(),"mmmyyyy")
Aug2022
?format(date(),"mmyyyy")
082022
Look At the Monthly Query, it is in the correct order but I cannot get the report in the same order.
 

jdraw

Super Moderator
Staff member
Local time
Today, 00:48
Joined
Jan 23, 2006
Messages
14,439
Not sure I have followed your posts, but Reports have their own Sorting and Grouping which overrides query sort/group.
See attached. I have altered the design to use Report Group and Sort values.

Design
DesignReportSortAndGroup.png

Report
ReportHasItsOwnSortingAndGroupiing.png
 

GPGeorge

Grover Park George
Local time
Yesterday, 21:48
Joined
Nov 25, 2004
Messages
788
?format(date(),"mmmmyyyy")
August2022
?format(date(),"mmmyyyy")
Aug2022
?format(date(),"mmyyyy")
082022
I was hoping the OP would explain what the desired output would look like. I already KNOW what Access produces. ;)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:48
Joined
Feb 19, 2002
Messages
36,961
The date format, whatever it is, is for HUMAN consumption. NEVER sort a formatted date unless it is in year, month, day order. ALWAYS sort the unformatted datetime field. If your data is not a real data but is just month and year, then you will have an easier time if you use two columns SomeYear, SomeMonth. Then you can sort on year and month but display as month and year. Also, never use year or month as column names. They will conflict with the Year() and Month() functions.
 

Users who are viewing this thread

Top Bottom