new user

P

PSHurd

I made a query that sorts birthdays between two dates ex. 05/01/** and 05/31/**
when my results appear they appear numerically by all the 1's first, then
the 2's. Ex. is 5/1, 5/10, 5/11, til it goes thru 1's then 5/2, 5/20,/5/21.
How do I get my database to search and keep the days in order of the month. I
tried to do it by ascending order and that wasn't the solution.
 
A

Arvin Meyer [MVP]

Your field is storing your "dates" as Text, since you are not including the
year. You'll need to store the "date" as:

05/01
05/02
05/03
etc.
 
J

John Spencer

Alternative - assuming the DatesOfBirth are in a date field.

Order By Month(DOB), Day(DOB)

In design view (query grid), add two fields
Field: TheMonth: Month([BirthdayField])
Sort: Ascending
Show: Unchecked

Field: TheDay: Day([BirthdayField])
Sort: Ascending
Show: Unchecked

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
E

Evi

Type in any year if you don't know the year of birth. Format the field so
that it only shows the month and year. I also added a tick field to indicate
that I didn't know the year of birth (that allowed me to calculate the age
of my aquaintances when I did know it).

Here is a function which you can paste into a module to calculate the date
of their next birthday: It ignores the year field so you can use it with a
dummy year and you can sort by this field.

Public Function NextBirthday(dtDate As Variant) As Date
Dim Been As Integer
Dim CurrentBirthday As Date

If Not IsNull(dtDate) Then
CurrentBirthday = DateSerial(Year(Date), Month(varBirthdate),
Day(varBirthdate))
'Check when their birthday falls on this current year
Been = Abs(CurrentBirthday(dtDate) < Date)
'if the birthday has already passed this year, then their next birthday is
next year.
NextBirthday = DateSerial(Year(Date) + Been, Month(dtDate), Day(dtDate))
End If

End Function


You can then put into your query

DaysToBirthday: NextBirthday([DOB])-Date()

So you know how many days you have to save up for that mega present.

(DOB is the date of birth field which may contain the birthdate with a
random year)


Evi
 
Top