Sorting approximate dates e.g., 5-72

  • Thread starter Thread starter robboll
  • Start date Start date
R

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
 
Are any of your fields blank? Does every text value in ApproxDTE convert to
a date reliably?
 
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.
Click to expand...

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]
 
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.
 
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
Click to expand...
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:
Click to expand...
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.
Click to expand...
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.
Click to expand...
Any help (without having to append to a seperate table) greatly
appreciated!!!
Click to expand...
RBollinger- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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!
 
Back
Top