var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Birthday Reminder
I have a simple table like this :
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 ?
Birthdays in the next two weeks
oh wait, you're using access...
where ( YEAR(DATEADD("d",10,DATE())) - YEAR(DOB) )
- ( IIF(
, 1,0 ) )
> ( YEAR(DATE()) - YEAR(DOB) )
- ( IIF(
, 1,0 ) )
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 ?
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
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
Last edited by playernovis; 01-18-03 at
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 ?
> 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
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 ?
I use this :
ORDER BY Abs(DateDiff("d",Format(Date(),'mmdd'),Format([DOB],'mmdd')));
Does this look fine ?
I test this, does not work properly.
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:
iif ( FORMAT(DATE(),'MMDD') < FORMAT(DOB,'MMDD')
, year(DATE()), year(DATE())+1 )
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.
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
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
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 ?
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