Question Birthday Reminder Query

[AccessNewBoy]

Hi all,
i have an issue with a Query i have created which shows all birthdays within a 21 day period. I have searched this forum and it appears the problem has been experienced by other people to yet the solutions suggested to not rectify the issue do not. so i was wondering if anyone has any other thoughts.

The issue is that the Query is fine until it reaches the end of the calender year and it will not see January onwards until 2010 starts. For example as you know we are in December now and all it will show is Decembers Birthdays but will not role over an see any in January. I have tried the following ideas yet they have not made any difference:

Between Format(DateAdd("d",-1,Date()),"mm/dd") And Format(DateAdd("d",21,Date()),"mm/dd")

Between Date() and Date() + 21

I have attached the Database in question which contains my Query in case i am missing something silly but i have ran out of ideas, so any help would be appreciated.

 

[Poppa Smurf]

You have two options

Option 1. Change the data type of your DateOfBirth to Date, then in your query place Between Date() And Date()+21 in the criteria of your DateOfBirth column. You have it as a text data type this will be a problem if you need to sort this field.

Option 2. In your query add a new column then in the field of this column enter new_date: CDate([dateofbirth]) in the criteria of this column enter Between Date() And Date()+21

 

[AccessNewBoy]

You have two options

Option 1. Change the data type of your DateOfBirth to Date, then in your query place Between Date() And Date()+21 in the criteria of your DateOfBirth column. You have it as a text data type this will be a problem if you need to sort this field.

Option 2. In your query add a new column then in the field of this column enter new_date: CDate([dateofbirth]) in the criteria of this column enter Between Date() And Date()+21

Hi,
thanks for taking the time to reply. Just out of curiosity did you successfully try both these suggestions in my Query? The reason i ask is that when i do none of them work. I am asking in case the error is mine

 

[boblarson]

Okay, this took some doing and I finally got it to work. Look at your revised query to see the formulas which were needed to make this work. (by the way, Bob Raskew or someone else might come in with a better solution, but this was the one I thought of).

 

[Poppa Smurf]

Here is my version. I changed your data type to date. The other table and query uses CDATE.

Bob
Why not use CDATE as this converts the date string to a date data type?

 

[boblarson]

Here is my version. I changed your data type to date. The other table and query uses CDATE.

Bob
Why not use CDATE as this converts the date string to a date data type?

Because I don't know if you noticed, but your "Birthday conversion" changed the date to the birthdays to have 2009 years and by doing the +21 it left off those in January which should have been on it. So, your sample is NOT working correctly. Check again...

 

[Poppa Smurf]

There was no dates for 2010 in the original database only dates for Jan 2009. When I added Jan 2010 these dates appeared within the 21 days so CDate works.

 

[boblarson]

There was no dates for 2010 in the original database only dates for Jan 2009. When I added Jan 2010 these dates appeared within the 21 days so CDate works.

What you don't seem to realize is that the user isn't going to want to change the dates every year. The person was NOT born on 12/28/2009 and so they are storing the month and day. My code will work FOREVER without making any changes to any tables or any dates. Get it? Your solution requires the user to make changes to each contact's birthday every year or else they won't show up in the list. This is bad programming. You want it to be DYNAMIC where the user has to do NOTHING with the data and it will still show up.

So, again, I will say it - My solution trumps yours as mine needs no changes to the data structure and no changes to the data.

 

[boblarson]

And all it took was this to make a new field which builds the correct date for the birthday:

BDay: IIf(Right([DateOfBirth],2)=12 And Format(DateAdd("d",21,Date()),"mm")=1,DateSerial(Year(Date()),CLng(Right([DateOfBirth],2)),CLng(Left([DateOfBirth],2))),DateSerial(Year(Date())+1,CLng(Right([DateOfBirth],2)),CLng(Left([DateOfBirth],2))))

And then the criteria is

Between Date() and Date()+21

 

[raskew]

Hi -

Here's an example based on Northwind's Employees table.

1) Public Function Anniversary

Public Function Anniversary(ByVal pDate As Date, _
                            Optional pNextOne As Boolean = False) As Date
'*******************************************
'Purpose:   Return this years' or the next
'           anniversary of an input date
'Source:    http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=48683
'Coded by:  raskew
'Inputs:    From debug (immediate) window:
'           Note: Today's date = 02-Jun-2003
'           HireDate = #4/23/99#
'           (1) ? Anniversary(HireDate, True)
'           (2) ? Anniversary(HireDate, False)
'           (3) ? Anniversary(HireDate)
'           HireDate = #8/23/99#
'           (4) ? Anniversary(HireDate, True)
'           (5) ? Anniversary(HireDate, False)
'           (6) ? Anniversary(HireDate)
'
'Output:    (1) 4/23/04
'           (2) 4/23/03
'           (3) 4/23/03
'           (4) 8/23/03
'           (5) 8/23/03
'           (6) 8/23/03
'*******************************************

Dim MyDate As Date

    MyDate = pDate
    
    'this line provides the anniversary date occuring this year
    MyDate = DateSerial(year(Date), month(MyDate), day(MyDate))
    
    'this line allows the user to specify the next scheduled anniversary
    'if this years' anniversary has already past
    'note that the second and third options come into play only if pNext = True
    MyDate = Switch(pNextOne = False, MyDate, MyDate < Date, DateAdd("yyyy", 1, MyDate), True, MyDate)
    
    Anniversary = MyDate

End Function

2) QryNextBD

SELECT
    LastName,FirstName
  , BirthDate
  , DateValue(Anniversary([BirthDate],True)) AS NextBD
  , DateDiff("d",Date(),Anniversary([BirthDate],True)) AS DaysTill
FROM
   Employees
WHERE
   (((DateDiff("d",Date(),Anniversary([BirthDate],True)))<=[COLOR="Red"]365[/COLOR]))
ORDER BY
   DateValue(Anniversary([BirthDate],True));

To limit the days in advance to display, change the 365 to whatever, e.g. 21.

Bob