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.

 

[Pat Hartman]

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.