Sorting

[Harmannus]

Hallo,

I have a query with the next code:

Sorting: Year(Date()) & Month([dateofbirth]) & Day([dateofbirth])

How can a sort ascending on this field? The results 2004101, 200411 etc. do
not get sorted correctly. The outcome should be 20041001, 20040101 etc. Any
suggestion on how to correct his.

Tried adding: format(Month([dateofbirth]);"mm") but that doesn't work...

Or can i sort on the dateofbirth related to the current year through another
trick with code added to it?

Thanx in advance for any tips.

Regards,

Harmannus

 

[John Vinson]

Hallo,

I have a query with the next code:

Sorting: Year(Date()) & Month([dateofbirth]) & Day([dateofbirth])

This builds a text string and loses the leading zeros... hence
incorrect sorting as you see!

Or can i sort on the dateofbirth related to the current year through another
trick with code added to it?

Try

Sorting: DateSerial(Year(Date()), Month([dateofbirth]),
Day([dateofbirth]))

This will give you a Date/Time value which will sort correctly; and
you can use criteria such as

BETWEEN Date() AND Date() + 7

to find the birthdays coming up in the next week.

 

[Harmannus]

Hallo,

Thanx for the reply.

Great help!

Regards,

Harmannus

Hallo,

I have a query with the next code:

Sorting: Year(Date()) & Month([dateofbirth]) & Day([dateofbirth])

This builds a text string and loses the leading zeros... hence
incorrect sorting as you see!

Or can i sort on the dateofbirth related to the current year through another
trick with code added to it?

Try

Sorting: DateSerial(Year(Date()), Month([dateofbirth]),
Day([dateofbirth]))

This will give you a Date/Time value which will sort correctly; and
you can use criteria such as

BETWEEN Date() AND Date() + 7

to find the birthdays coming up in the next week.