Sort on Short Text Date in mm/dd/yyyy format

I should also add that I am doing this sorting in a crosstab query.

Thank you for this. Is there a way to reformat a short text field in the format 1/2015 into a non-character field so I can short numerically.

Basically, I will need to create a new field where the short text field is reconfigured as a date/time field so I can easily sort on this.

The  reason I re-formatted is so that I can output the results into an excel file that will then go into a report, and the date needs to be formatted this way.

Thank you everyone.

A few other notes.

If you have a date,...store it as a real date.
Technically 12/2015 is not a date

You can enter the date as 1/1/2015, (always the first of the month) ...then format it as 1/2015
Then you can create a query that displays just the month/year, ...but sorts by the real date.
Something like this:
SELECT YourTable.ID, Format([YourDate],"mm/yyyy") AS Expr2
FROM YourTable
ORDER BY Format([YourDate],"yyyy-mm-dd");

If you keep storing your "dates" as just month and year, ...you will be forever dealing with sorting an conversion issues.

JeffCoachman

Yes, this makes sense.

The issue is I need to have it as a result on a cross tab table where the results are only grouped by month, but I have a consecutive dd/mm/yyyy field to work from - so I have to group all days into one month.

Hope this makes sense -

I start with day/time field mm/dd/yyyy

Have to convert to short text m/yyyy for reporting purposes

Need to sort on short text m/yyyy to get the months aligned in the final crosstab

But sorting is an issue, and the only way I see it is to convert the short text m/yyyy back to a date/time format for proper sorting.

The crosstab query sorts the column headers so unless you format them as I suggested, the columns will be in alphabetic order rather than numeric order.  The only way to control this is to create a column headers property.  That works fine for a single year so you can have your headers named Jan, Feb, Mar, Apr, etc.  If you don't use a column headers property, then you end up with months in alpha sequence and Apr is the first month of the year.  Same goes for this.  If you must have the field formatted as 1/2015, then you must create column headers and you MUST modify the query every time the set of column headers changes.  The query will NOT return data if it doesn't match the column headers list.  So if you add 1/2015, 2/2015, 3/2015 because that is all the data you have now, the rest of the months won't magically appear.  You will need to modify the column header property.

I have never worked with column header property - I will work with this.

Thank you for the suggestion. You are all the best.

I don't believe that column headers are appropriate for variable headings.  But, as long as you keep on top of the settings, you can make them work.  Make sure you document what you are doing and set a reminder in your calendar reminding you when to change them.