Sorting

  • Thread starter Thread starter Harmannus
  • Start date Start date
H

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
 
Hallo,

I have a query with the next code:

Sorting: Year(Date()) & Month([dateofbirth]) & Day([dateofbirth])
Click to expand...

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?
Click to expand...

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.
 
Hallo,

Thanx for the reply.

Great help!

Regards,

Harmannus

John Vinson said:
Hallo,

I have a query with the next code:

Sorting: Year(Date()) & Month([dateofbirth]) & Day([dateofbirth])
Click to expand...

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?
Click to expand...

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.
Click to expand...
 
Back
Top