Sort dates that are only month and year

[dk4300]

Hi!
I have a table that has 'Year' and 'Month'. 'Year' is 4-digit, "Month" is 2 digit. In a query, I want to combine it into one field and sort in order.

On everything I've tried, since the day is missing, it thinks it's a text field.
Even though I can get it to look like "02/2006", it sorts it as text (02/2006, 02/2007, 03/2006, 03/2007) instead of 02/2006, 03/2006, 02/2007, 03/2007.

Thank you!

[Rabbit]

Put year first.

[maxamis4]

my only suggestion at this point is get rid of the / and just combine the dates as a number. Formatting dates is something that usually occurs in the begining. There a dozen tools that will easily let you go restort back to this formatt for report reasons. Such as left([yourdate0, 2) will give you the month. So again suggestion for sorting just leave it as a number.

ps

right([yourdate], 4) will give you the year. Just in case you need it.

That should give you the first two digits of the month

Hi!
I have a table that has 'Year' and 'Month'. 'Year' is 4-digit, "Month" is 2 digit. In a query, I want to combine it into one field and sort in order.

On everything I've tried, since the day is missing, it thinks it's a text field.
Even though I can get it to look like "02/2006", it sorts it as text (02/2006, 02/2007, 03/2006, 03/2007) instead of 02/2006, 03/2006, 02/2007, 03/2007.

Thank you!

[NeoPa]

You don't make it very clear what format the data is stored in. If it's a Date/Time field then the answer is different from a string or even two separate fields.
Please include the MetaData for your table. Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

The solution is not difficult. Understanding the question is the problem here.

[dk4300]

Put year first.

Thanks. I ended up saving it as a text field w/ the year first. I should have thought of that, but I was really wanting there to be a way that easy while having it a date field. :)
Thanks!

[Rabbit]

Thanks. I ended up saving it as a text field w/ the year first. I should have thought of that, but I was really wanting there to be a way that easy while having it a date field. :)
Thanks!

Not a problem.

However, you never said it was a date field. If it was a date field, it would sort properly. You had 2 seperate fields, both numbers, combining them makes it a string with the format month/year. This is not a date field even though it represents a date field. You could convert it to a date field but date fields require a day in addition to month and year.

[NeoPa]

Thanks. I ended up saving it as a text field w/ the year first. I should have thought of that, but I was really wanting there to be a way that easy while having it a date field. :)
Thanks!

There is of course, but until you respond to post #4 we can't really tell you what it is.
Ironic isn't it.