Date comparison

[Guest]

I have a query where i am checking to see if a field is a date. If not then I
default a date to it. Otherwise I use it. I am trying to use this to sort the
query so the results are in date order. It does not return in the right order
when I am testing. Can someone tell me what is wrong with this code.

iif(isdate(horse.horseage) = -1,
CDate(iif(isnull(horse.horseage)<>0,'1/1/1950',horse.horseage)),'')

I know the field should be a date field but that is not an option with what
I am working on.

 

[Tom Ellison]

Dear Dale:

I think you have not looked at what your code produces. Display the column
that does this, with the horsesage along side it.

For what I expect you want, you have turned it around. I think what you
want may be:

IIf(IsDate(Nz(horse.horseage, "#01/01/1950#)), Nz(horse.horseage,
#01/01/1950#), #01/01/1950#)

Use # around a date literal. Sorting by a string will not be date order.

Any closer?

Tom Ellison