Query Sort Problem

Novice1

Registered User.
Local time
Yesterday, 22:38
Joined
Mar 9, 2004
Messages
385
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
 
I suspect you're creating an alphabetic sort. Try the Nz() function instead:

Nz(RetirementDate, SeparationDate)
 
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.
 

Users who are viewing this thread

Back
Top Bottom