Query Sort Problem

[Novice1]

In each record I have two date fields (retirementdate and separationdate). In the record one of the date fields will have a date. The other field will be left blank.

I want to combine the two date fields into one field (combineddate) then sort the data (ascending). I created a new field. The dates show correctly but the data doesn't sort correctly.

CombinedDate: [RetirementDate] & [SeparationDate]

Thinking my problem may be format related, I modified the field to read:

CombinedDate: Format([RetirementDate] & [SeparationDate],"dd mmm yy")

Again the dates show correctly but I cannot sort the data (ascending).

What am I doing wrong? Thanks

 

[pbaldy]

I suspect you're creating an alphabetic sort. Try the Nz() function instead:

Nz(RetirementDate, SeparationDate)

 

[sneuberg]

I found that having a computation in a field that is sorted can give you performance problems. Most sorting algorithms need to access the field for comparison at least N log N times. If this becomes a problem I suggest actually combining the fields and have another field to indicate whether it is a retirement or separation date.