Custom Input Mask To Sort

[dubczech]

I need a field that I would like to create as a date field, however this needs to hold only the month, and the month has to show as a 3 letter abbreviation (APR, MAR, JUL, NOV, etc.).

I will need to have this sort (so MAR comes before JUL) by the order of the month.

Any ideas?

 

[Access_guy49]

Do your sorting in a query.
First you select your field that holds your 3 letter month. then you create another field in the query manually. Sort by that second field because it's a number.

you can create a field:

 Expr: FORMAT([Tablename]![Date],"mm")

This will give you your month value only in a number. you then sort this field accending. you can uncheck the field if you don't want it to be displayed.

 

[dubczech]

the field where the 3 letter month data is currently a text field. Do I need to change this?

I did a quick query with the code below, and it brought over the 3 letter month into a 3 letter month, and when sorted, it sorted alphabetically.

What have I missed?

 

[Access_guy49]

yes, being that it is a text field, it wont be able to convert that to a number. your 3 letter date field needs to be a date field.

 

[Access_guy49]

If you want to keep your text field, you can create a reference table that associates each three letter month with a number. Then you would simly query based on those 2 tables and sort according to your number field (just hide it with an uncheck)

Generally i keep full dates in my actual tables and just manipulate displays in query. I believe access stores dates in serial and therefor you wont be able to store only a month as a date field.