Sorting on a text field that displays mm/yy

[Marv Trott]

I need to sort a text field in date that currently displays mm/yy.

Any suggestions would be appreciated.

Thanks in advance.

Marv Trott

 

[Duane Hookom]

Try sort on the expression:
SortColumn:Right([ATextField],2) & [ATextField]

 

[Trever B]

Hi Mary,

Not sure what you mean but if following try:-

If date is within a text field mm/yy then try this

I created a table called olddate with field call olddate then ran this query

SELECT Olddated.OldDate
FROM Olddated
ORDER BY Right([Olddate],2) & Left([Olddate],2);


Trev B

 

[Van T. Dinh]

If the Field value is of date datatype, you still can sort by date
regardless of the display Format Property setting.

If you use date-to-String conversion functions such as Format(), then source
by the source Field and not the calculated Field that uses Format().

 

[Marshall Barton]

I need to sort a text field in date that currently displays mm/yy.

Try sorting on DateSerial(Right(field,2). Left(field,2), 1)

 

[Marv Trott]

Marsh,

Sorry, I did not correctly explain properly what I was trying to do.

I have a query that uses data from a linked table that I do not control. The
query allows the user to select a date range for the data to be displayed.

The data field in the in the old table was text and looked like a date
mm/dd/yyyy. I used the Cdate() to convert it to a date field and then the
user could enter the start date mm/dd/yyyy and the end date mm/dd/yyyy.

The table has been changed and now the text in the date field looks like
this mm/yy. The user will now enter a start date mm/yy and an end date
mm/yy.

The Cdate() function does not seem to work on mm/yy.

Thanks.

Marv

 

[Duane Hookom]

Marv,
Did you even try any of the suggestions? Marsh's solution creates a nice
date value from your text value.

--
Duane Hookom
MS Access MVP

Marsh,

Sorry, I did not correctly explain properly what I was trying to do.

I have a query that uses data from a linked table that I do not control.
The query allows the user to select a date range for the data to be
displayed.

The data field in the in the old table was text and looked like a date
mm/dd/yyyy. I used the Cdate() to convert it to a date field and then the
user could enter the start date mm/dd/yyyy and the end date mm/dd/yyyy.

The table has been changed and now the text in the date field looks like
this mm/yy. The user will now enter a start date mm/yy and an end date
mm/yy.

The Cdate() function does not seem to work on mm/yy.

Thanks.

Marv

 

[Marshall Barton]

Sorry, I did not correctly explain properly what I was trying to do.

I have a query that uses data from a linked table that I do not control. The
query allows the user to select a date range for the data to be displayed.

The data field in the in the old table was text and looked like a date
mm/dd/yyyy. I used the Cdate() to convert it to a date field and then the
user could enter the start date mm/dd/yyyy and the end date mm/dd/yyyy.

The table has been changed and now the text in the date field looks like
this mm/yy. The user will now enter a start date mm/yy and an end date
mm/yy.

The Cdate() function does not seem to work on mm/yy.

CDate does what it says it will do. Unfortunely, you want
it to do something else. A string of the form a/b will be
interpreted as mm/dd and CDate will automatically supply the
current year.

I think you could have avoided all this if you just leave
the table field as you originally converted it. It's up to
the query to manipulate it to meet your criteria's needs.

It's always possible to not use unneeded information, but it
is impossible to make up reliable data from partial
information. Eg. You could easily set the format for the
date field to mm/yy, but you can no longer determine the day
of the month after discarding that information.

For example, if the table's date field has arbitrary dates,
then the query can use a calculated field with an expression
like:
MyDate: Format(datefield, "yyyymm") Between
Format(CDate("1/" & [Start Date], "yyyymm") And
Format(CDate("1/" & [End Date], "yyyymm")
with a criteria of True

As indicated by the various replies to your original
question, there are many variations that can be used for
this purpose.

You can get what you asked using the above by using
Format("1/" & funkytextdatefield, "yyyymm")
but it sure is going the long way around the issue.

 

[Marv Trott]

Duane,

I guess my understanding of Access is to limited to be able to apply
Marsh's solution to my question since I did not ask the correct question.

This is from the query I used on the original data table:

FIELD Counsel Date: CDate([F641A!CounselDate])
TOTAL Group By
CRITERIA Between CDate([Enter Start Date mm/dd/yyyy]) And CDate([Enter End
Date mm/dd/yyyy])

How do I use the conversion that March suggested to enter it in this query
so I can use the new date text mm/yy?

FIELD Counsel Date: ??????????????
TOTAL Group By
CRITERIA Between [Enter Start Date mm/yy and mm/yy] And [Enter End Date
mm/yy]

Is there something I have to do to the text data before I can use it in this
query? If so what is it?

Thanks for your patience.

Marv

Marv,
Did you even try any of the suggestions? Marsh's solution creates a nice
date value from your text value.