sort Data fields (Access2002)

Topic

New Reply

How do you sort Data fields that 1/2/2005 comes after 12/20/2004? Datatype is Text.

Reply | Quote

Replies

I would think that you need to change it to date/time datatype

Reply | Quote

Changing the data type is the best solution.

If you really want to keep it as text you could parse out the year in a query with Right(YourData, 4) & then sort on the result. This would order on the year only.

Reply | Quote

Changing the data type is the best solution.

If you really want to keep it as text you could parse out the year in a query with Right(YourData, 4) & then sort on the result. This would order on the year only.

Reply | Quote

I would love to but impossible task.
There is Excel file that gets modified and I am pulling datat from this file and it is Text.
Data is very abnormal as user gets to it easily, so I have to do it as simple as possible. all fields are text.
Thanks

Reply | Quote

Create a query based on the table. Add a calculated column:

RealDate: DateValue([TextDate])

where TextDate is the name of the field that contains text values that look like dates. RealDate is the name of the calculated column, it can be anything. Sort on this column.

Reply | Quote

THANKS it does work!!!

Reply | Quote

THANKS it does work!!!

Reply | Quote

Create a query based on the table. Add a calculated column:

RealDate: DateValue([TextDate])

where TextDate is the name of the field that contains text values that look like dates. RealDate is the name of the calculated column, it can be anything. Sort on this column.

Reply | Quote

I would love to but impossible task.
There is Excel file that gets modified and I am pulling datat from this file and it is Text.
Data is very abnormal as user gets to it easily, so I have to do it as simple as possible. all fields are text.
Thanks

Reply | Quote

I would think that you need to change it to date/time datatype

Reply | Quote