new user

  • Thread starter Thread starter PSHurd
  • Start date Start date
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.
 
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.
 
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
 
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
 
Back
Top