Sort on Calculated Date field

[cardgunner]

I have an expression in a query

Expire: IIf([payterm]="X","",DateAdd([payterm],1,[orderdate]))

However when I sort it it does not sort in correct manner

it's goes like

1/11/2007
1/15/2008
10/10/2006
10/16/2007
10/31/2007
10/5/2006

I have the field properties set to Short Date.

What do I need to do for this to sort right?

 

[raskew]

Does [PayTerm], when you invoke the DateAdd() function, equate to "yyyy", "m", "day" or any of the valid intervals (see help file).

It appears [OrderDate] is stored as text rather than DateTime data format. That would account for the sorting you are experiencing.

See this MSKB article on how Access stores dates:
http://support.microsoft.com/kb/q130514/

When storing a date as text, you lose the availability of many Access date functions.

Suggest you consider converting text date to date/time data format.

HTH - Bob

 

[cardgunner]

Thanks for the reply.

Yes payterm is yyyy, d, m, q, or X

Order date is a date/time.

I took out the "" from the expression and replaced it with [order date] and it sorts right.

I also tried a any date such as #01/01/1975# and that worked.

But I kept it as [orderdate]. I had to change some other things in where I use the query for the user to understand.

I still don't like it. It's confusing for the user.

I was actually thinking of doing a conditional format where if payterm="x" then make the font white so you wouldn't be able to see it. That just feels wrong and I fell that I'm cob jobbing my work if you are familiar with that term.