Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Birthday Query (97/SR2)

    I can't figure this out. I set up a report, based on a query, with help from people on this board. It worked great, thank you, thank you. Now, with the new year, it doesn't work anymore but I can't see where the year would have anything to do with it. The problem is in the query, once that is fixed, then the reports should work fine.

    I have a query that lists all of our students. I created a new field in the query called Birthdate, which is based on the field Students Birthdate. The purpose of the new field is to show only the month and day of the birthdate. The new field looks like this:Birthdate: Format([Students birth date],"mm/dd"). When I remove the criteria from the query and then run it, I get just what I want. The criteria seems to be the problem, because when I put it back in, I get zero records and I know I should be getting some. The criteria is: >Now()+1 And <Now()+10. What I am looking for is anyone that has a birthday coming up in the next 10 days. When I run it with the criteria, I get nothing, even though I do have some birthdays coming up in the next 10 days. As I said before, this used to work and now it doesn't. (the last time I ran it was probably December 9, then when I tried to run it on January 6, it wouldn't work.)

    Any help would be greatly appreciated. Thank you.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Birthday Query (97/SR2)

    The function Now() contains the year also.
    >Now()+1 and <Now()+10 means Between 11january 2002 and 21 january 2002, according today is 10 january 2002.
    All depends from what is in the [Students birth date]. Is this a date type field ? If yes, then there is a year in it also even if is entered in a field with Format "mm/dd".
    You will have to use something like
    where CStr(Format([Students birth date],"mm/dd")) Between CStr(Format(Now()+1,"mm/dd")) And CStr(Format(Now()+11,"mm/dd"))
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Birthday Query (97/SR2)

    Why not something simpler?

    I'm assuming this is in your criteria you are talking about.

    (Month([BirthdateField]) = Month(Date())) AND (Day([BirthdateField]) Between Day(Date()) and Day(Date())+10
    Which Access will make into two calculated fields:
    Month([BirthdateField])
    Day([BirthdateField])
    With Criteria for each.
    Seems much simpler than using Format to me.
    FWIW

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Birthday Query (97/SR2)

    Hi Tom,
    That would only work if all ten days were within the same month. If you were at Feb 24th say, it would only return dates in the next 10 days that were also in February, rather than rolling over into March as well.
    You could add a calculated field:
    dateadd("yyyy",year(date())-year([birthdate]),[birthdate])
    and set a criteria for that of:
    between date() and date()+10
    and that should work, I think.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Birthday Query (97/SR2)

    Francois,

    Sorry I haven't gotten back to you sooner. I tried your method last week and it didn't work. The others didn't either and it's been one of those weeks, so I haven't gotten back to you sooner. I know just enough about this stuff to be dangerous. I didn't recognize the CStr and couldn't find it in the help. Today, just for the heck of it, I tried it without it and I got the results that I wanted. What is CStr? I am using A97, is it possible that this is a function for a later version? Anyway, I'm thrilled that it works. Thanks to you and all who helped out.

Posting Permissions

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