Sort values in a query

[Khalil Handal]

Hi to all,


In an access table a field has a Data Type as: Short text

This fields show values as : MMM YYYY (Apr 2025, Feb 2024, Mar 2025)
A query is built based on that table.

Is there a way to sort these values in the query descending so that Apr 2025 is before Mar 2025 ?
Can this be done without changing the Data Type to Date in the original table?

For example:
Apr 2025
FEB 2024
JAN 2025
Mar 2025

Needs to be shown as:
Apr 2025
Mar 2025
JAN 2025
FEB 2024


Sincerely
Khalil

[Welshgasman]

Yes, use the month and year numbers and sort on them?
Should always store dates as dates, even if you had to have the first of every month/year.

[CJ_London]

You need the year and a numeric value for the month 1 for Jan, 2 for Feb etc

[Khalil Handal]

Yes, use the month and year numbers and sort on them?
Should always store dates as dates, even if you had to have the first of every month/year.

I always store dates as dates. In this case the field is linked to an Excel sheet where the format of the cells are "numbers".

[Welshgasman]

I always store dates as dates. In this case the field is linked to an Excel sheet where the format of the cells are "numbers".

So just convert to a date and then sort by Year and Month.

Edit: Are you sure they are not actually dates?, just formatted as that? That is the way I would have it. Actual Dates and formatted as mmm yyyy?

[Khalil Handal]

Edit: Are you sure they are not actually dates?, just formatted as that? That is the way I would have it. Actual Dates and formatted as mmm yyyy?

IT has an input mask of: >L<LL\ 0000;0
for the text box.

[Welshgasman]

IT has an input mask of: >L<LL\ 0000;0
for the text box.

I did not know Excel had input masks?

[ArviLaanemets]

Hi to all,
In an access table a field has a Data Type as: Short text

This fields show values as : MMM YYYY (Apr 2025, Feb 2024, Mar 2025)

Whenever I need to store any quarter, month, or week week number in any database, I use integer values in formats YYYYQQ, YYYYMM, or YYYYWW. It ensures those values can always to be ordered properly, and are valid for any regional settings (except week numbers, as those depend on specific region). I.e.
202504 instead of "Apr 2025",
202402 instead of "Feb 2024",
202503 instead of "Mar 2025".

Also at workplace I had a calendar table available (not anymore currently, as I am on pension now, and haven't created one at my home computer jet), with date as PK field, and various additional fields like those quarter, month and week numbers, but also fields for indicating state holidays (0 or 1), or workdays (0 or 1), or fields containing normative working hours, or field containing number of days to current or previous workday (0 or a number), or field containing number of days to current or next workday (0 or a number), etc. You also can have there a field(s) which contains date texts like those you currently have (e.g. "Apr 2025").
The calendar table contained all dates from some starting one and into some number of years into future. All additional fields were stored as values, and I had a procedure to add new dates and values to calendar table whenever there was a need for this.
In case the database using the calendar table was in local LAN only, the table was stored in calendar database available to any database in same LAN, and linked to database using it, or queried from calendar database. In case the database was sent to some outside user, or the link to calendar table was not guaranteed for some reason, a copy of calendar table was added into database.
This calendar table is useful for any calculations for specific dates, or specific date periods, or for creating reports, etc.

[June7]

Clarify. First you say it is an Access table then you say field is linked to an Excel sheet. This makes no sense. This "table" is a linked worksheet?

And input mask in what textbox where?

If these values are treated as text by Access, need to convert to number values. Parse the string into its date parts using string manipulation functions. Might want to build a custom function that does this and returns YYYYMM number.

[Welshgasman]

@June7

CDate() will do the job in one function.

[June7]

Oh, that's right. Didn't realize could convert just mon year string.

ORDER BY CDate(fieldname)

"Apr 2025" becomes 4/1/2025

[Gustav]

Convert to date values and order by these:

Select * From YourTable 
Order By CDate([YourTextMonthYearField]) Desc

[Khalil Handal]

I did not know Excel had input masks?

The input mask is in access not in Excel

[Khalil Handal]

Thank you all.
Yes the CDate() Solved the issue.