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

I have a date field in Short Text format mm/dd/yyyy that I want to sort from most former date to current.

Unfortunately, the sorting appears as

1/2015
12/2015

Rather than

1/2015
2/2015

Thank you
exp vgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

exp vgAuthor Commented:
I should also add that I am doing this sorting in a crosstab query.
0
PatHartmanCommented:
The problem is that you have formatted a date as a string and so it will sort like a string - character by character, left to right.  The only way to get this string to sort as a date, is to format it such that the most important part is to the left AND you have to zero fill the month.  So

2015/01
2015/02
2015/12

if you don't use leading zero, you'll end up with:
2015/1
2015/12
2015/2
0
exp vgAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
Why not just use the dateTime field you mentioned exists in one of your other questions?

I don't see any good reason to convert a datetime field into a text field.  If you need the data formatted in a particular text format for a report or form, you can always create a computed field using the Format( ) function

SELECT [DateField], Format([DateField], "yyyy-mm") as YrMo
FROM yourTable
ORDER BY [DateField]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
try this

SELECT [DateField], Format([DateField], "yyyymm") as YrMo
 FROM Tablex
 ORDER BY Format([DateField], "yyyymm")
0
exp vgAuthor Commented:
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.
0
exp vgAuthor Commented:
Thank you everyone.
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
exp vgAuthor Commented:
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.
0
PatHartmanCommented:
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.
0
exp vgAuthor Commented:
I have never worked with column header property - I will work with this.

Thank you for the suggestion. You are all the best.
0
PatHartmanCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.