1. Registered User
Join Date
Apr 2002
Posts
168

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. SQL Consultant
Join Date
Apr 2002
Location
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(DOB,'MMDD')
, 1,0 ) )
> ( YEAR(DATE()) - YEAR(DOB) )
- ( IIF(
FORMAT(DATE(),'MMDD')
< FORMAT(DOB,'MMDD')
, 1,0 ) )```

rudy
http://r937.com/

3. Registered User
Join Date
Apr 2002
Posts
168

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

4. SQL Consultant
Join Date
Apr 2002
Location
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. Registered User
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. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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

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. Registered User
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. Registered User
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. Registered User
Join Date
Apr 2002
Posts
168
I test this, does not work properly.

11. SQL Consultant
Join Date
Apr 2002
Location
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. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002