data field type conversion within a query possible?

  • Thread starter Thread starter Di
  • Start date Start date
D

Di

Can you tell me what syntax I need to use for the
following in a query:

I want to take a text field and sort it - but it's really
a date field. I MUST leave it text, but (of course) it
sorts incorrectly as a date. (ie: 01/01/04 comes before
10/10/01)

Thanks - in advance!
Di
 
Dear Di:

First, I'd suggest you make sure the values all convert to dates
properly. To do this, write a query that shows the date text column.
Add a calculated column next to it that shows:

CDate(YourDate)

You will be able to see how the dates convert and verify this is what
you want.

Finally, using that calculated column, sort the query ascending or
descending, as required. You can then uncheck the Display box so it
doesn't display this value. It will only sort by it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks Tom!
-----Original Message-----
Dear Di:

First, I'd suggest you make sure the values all convert to dates
properly. To do this, write a query that shows the date text column.
Add a calculated column next to it that shows:

CDate(YourDate)

You will be able to see how the dates convert and verify this is what
you want.

Finally, using that calculated column, sort the query ascending or
descending, as required. You can then uncheck the Display box so it
doesn't display this value. It will only sort by it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts




.
Click to expand...
 
Back
Top