Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Apr 2002
    Posts
    168

    Unanswered: Birthday Reminder

    I have a simple table like this :

    Name DOB
    John 12/20/1980
    Smith 01/05/1985


    I have a form with a list box. What I want is to populate the listbox with all names that has a birthday within 10 days from today.

    Does anyone know how to do this since the birth year is not used in this case ?



    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yup, see Birthdays in the next two weeks

    oh wait, you're using access...
    Code:
    select [Name]
      from simpletable
     where ( YEAR(DATEADD("d",10,DATE())) - YEAR(DOB) )
           - ( IIF(
                 FORMAT(DATEADD("d",10,DATE()),'MMDD')
               < FORMAT(DOB,'MMDD')
              , 1,0 ) )
         > ( YEAR(DATE()) - YEAR(DOB) )
           - ( IIF(
                 FORMAT(DATE(),'MMDD')
               < FORMAT(DOB,'MMDD')
              , 1,0 ) )



    rudy
    http://r937.com/

  3. #3
    Join Date
    Apr 2002
    Posts
    168
    Thanks for the quick reply.

    One thing I am still not sure is why do you include year(DOB) while the birth year does not really matter ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    because DOB is used to calculate age

    if you read the article, the strategy is to calculate age today, then calculate age 10 days from now, and if they're different, then the person had a birthday in the next 10 days

    calculating age goes like this --

    1. subtract the year of your birth from the current year

    2. if the month/day of today is before the month/day of your birthday this year, then subtract 1 from the number you got in step 1

    repeat the calculation for your age 10 days from now

    get it?


    rudy

  5. #5
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    this is not as universal and nice as Rudy's Solution, but it works in MS Access too...

    SELECT * FROM MyTable
    WHERE DateDiff("d",Now(),Day([MyBirthday]) & '/' & Month([MyBirthday]) & '/' & Year(Now())) <= 10




    jiri
    Last edited by playernovis; 01-18-03 at 23:15.

  6. #6
    Join Date
    Apr 2002
    Posts
    168
    Thanks a lot, that really helps.

    However, I still have one more question.

    When you compare format('mmdd'), does it also compare the year, or only strictly the month and day ?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    > When you compare format('mmdd'), does it also
    > compare the year, or only strictly the month and day ?

    no, just the month and day

    follow this example:

    suppose your date of birth was may 5 1979

    today is jan 21 2003

    subtract the years: 2003 - 1979 = 24

    are you 24 today? no

    because '0121' < '0505', you have to subtract 1

    i.e. you are 23 today, and will not be 24 until your birthday this year

    after that, and for the rest of 2003, you subtract 0




  8. #8
    Join Date
    Apr 2002
    Posts
    168
    Thanks again, it's all clear right now.

    Besides that, I also want to sort it by the the closest/earliest birthday. I use order by month(DOB) and day(DOB), it works for most cases, but not all. If today is 25 dec, and there are birthday at 29 dec and 2 jan, the 2 jan will be listed first and 29 dec will be listed last, which are not correct, since 29 dec will occur first.

    Do you know how to do this ? Can we use Datediff ?


    Thanks

  9. #9
    Join Date
    Apr 2002
    Posts
    168
    I use this :

    ORDER BY Abs(DateDiff("d",Format(Date(),'mmdd'),Format([DOB],'mmdd')));

    Does this look fine ?

  10. #10
    Join Date
    Apr 2002
    Posts
    168
    I test this, does not work properly.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    assume the WHERE clause works properly to select everyone who has a birthday in the next ten days

    to sort those people into order by closest/earliest birthday, try this:

    order by
    iif ( FORMAT(DATE(),'MMDD') < FORMAT(DOB,'MMDD')
    , year(DATE()), year(DATE())+1 )
    , FORMAT(DOB,'MMDD')

  12. #12
    Join Date
    Apr 2002
    Posts
    168
    Wow, thanks a lot, it works fine.

    However, I am still not sure how it works since you put iif in the order by. Does it mean that there is a new variable created which value can be either year(date()) or year(date())+1 and that variable is used as a sort variable ? The first impression I get from the iif in order by is that the sort is based on a value, not a variable (because iif returns a value).

    Can you give an example.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, the IIF in the ORDER BY produces a value

    it produces a value for each row, and the value it produces on each row is determined by the values of the columns on that row that are used in the IIF

    add the two ORDER BY columns to the SELECT list and you will see your example


  14. #14
    Join Date
    Apr 2002
    Posts
    168
    Hmm interesting, because I did not know that you can this before. In this case, usually I do this :

    select ..., iif ( FORMAT(DATE(),'MMDD') < FORMAT(DOB,'MMDD')
    , year(DATE()), year(DATE())+1 ) as my_year,format(DOB,'mmdd') my_birthday
    where ...
    order by my_year,my_birthday

    because I used to think that I always have to put a variable in the order by, not a value. Thanks, learn something new from you.

    My code should just work the same as yours, right ?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, your code should work...

    ... unless access decides it will not allow you to use the column aliases my_year and my_birthday

    try it and see


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •