Change a Date Format

[Tom d]

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]

Sounds like "mmmmyyyy" would work, have you tried it? What happened?

 

[plog]

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

 

[Tom d]

Sounds like "mmmmyyyy" would work, have you tried it? What happened?

Tried =Format([Date-Entered By Month],"mmmmyyyy")
Received error #Type!

 

[MarkK]

There are no queries in your database.

 

[MarkK]

SELECT tlbExpenses.ID, tlbExpenses.Category, Format([Date_Entered],"mmmmyy") AS MyDate
FROM tlbExpenses;

This query works for me in your database.

 

[GPGeorge]

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:

 

[theDBguy]

Tried =Format([Date-Entered By Month],"mmmmyyyy")
Received error #Type!

This worked for me.

Here's the Design View:

Edit: Oops, too slow...

 

[GPGeorge]

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]

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]

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

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


Report

 

[GPGeorge]

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