Sorting approximate dates e.g., 5-72

[robboll]

I am trying to sort an MS Access 2003 database with a text field:
ApproxDTE with entries like

5-72
8-58
9-84
..
..
..
As text these do not sort correctly. If I do something funky like
datevalue([ApproxDTE]) it gives me values like 5/1/72, 8/1/58, 9/1/84
-- which is great -- but don't try to put in in Assending order. It
fails with the error:

This expression is typed incorrectly, or it is too complex to be
evaluated. For eample, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables.

If I use: Format(DateAdd("d",7,[ApproxDTE])) it returns values like:
5/8/72, 8/1/58, 9/1/84 which is fine too, and you can use Assending
order -- but it doesn't sort correctly.

Any help (without having to append to a seperate table) greatly
appreciated!!!

RBollinger

 

[Duane Hookom]

Are any of your fields blank? Does every text value in ApproxDTE convert to
a date reliably?

 

[John W. Vinson]

I am trying to sort an MS Access 2003 database with a text field:
ApproxDTE with entries like

5-72
8-58
9-84
.
.
.
As text these do not sort correctly.

Well... they do sort correctly, in that a text string beginning with "5"
should and will sort before a text string beginning with "9". But you're
right, they don't sort as dates (because they aren't dates)!

ASSUMING that you can live with Access' convention that two digit years from
30-99 are in the 20th century and 00-29 are in the 21st, try putting in a
calculated field:

RealDate(CDate(Replace([ApproxDTE], "-", "/1/"))

This will convert 5-72 to 5/1/72, and then convert that to a Date/Time value
#05/01/1972# - which will sort chronologically.


John W. Vinson [MVP]

 

[David Cox]

I am using UK dates.

If I make your sample date fields type date/time they are stored as 1/5/72,
1/8/58 etc, which results in them being sorted into the correct order. The
first of the month is defaulted. I suspect that he same will be true using
American dates.

However 1-06 converts to 1/6/2007 - the second part is assumed to be a month
and the current year is defaulted.

 

[robboll]

Are any of your fields blank? Does every text value in ApproxDTE convert to
a date reliably?

--
Duane Hookom
MS Access MVP

I am trying to sort an MS Access 2003 database with a text field:
ApproxDTE with entries like

5-72
8-58
9-84
.
.
.
As text these do not sort correctly. If I do something funky like
datevalue([ApproxDTE]) it gives me values like 5/1/72, 8/1/58, 9/1/84
-- which is great -- but don't try to put in in Assending order. It
fails with the error:

This expression is typed incorrectly, or it is too complex to be
evaluated. For eample, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables.

If I use: Format(DateAdd("d",7,[ApproxDTE])) it returns values like:
5/8/72, 8/1/58, 9/1/84 which is fine too, and you can use Assending
order -- but it doesn't sort correctly.

Any help (without having to append to a seperate table) greatly
appreciated!!!

RBollinger- Hide quoted text -

- Show quoted text -

You are suggesting that datevalue() should work if I don't have blank
dates. I do have some blank dates and that may be the problem. I'll
have to check it out. Thanks!