Think I got it
Try this:
SELECT People.Brithdate,
IIf(DateAdd("yyyy",Year(Date())-Year([People].[Brithdate]),[People].[Brithda
te])<=Date(),DateAdd("yyyy",Year(Date())-Year([People].[Brithdate])+1,[Peopl
e].[Brithdate]),DateAdd("yyyy",Year(Date())-Year([People].[Brithdate]),[Peop
le].[Brithdate])) AS Birthday
FROM People
WHERE (((People.Brithdate) Is Not Null) AND
((IIf(DateAdd("yyyy",Year(Date())-Year([People].[Brithdate]),[People].[Brith
date])<=Date(),DateAdd("yyyy",Year(Date())-Year([People].[Brithdate])+1,[Peo
ple].[Brithdate]),DateAdd("yyyy",Year(Date())-Year([People].[Brithdate]),[Pe
ople].[Brithdate]))) Between Date() And DateAdd("m",1,Date())))
ORDER BY
IIf(DateAdd("yyyy",Year(Date())-Year([People].[Brithdate]),[People].[Brithda
te])<=Date(),DateAdd("yyyy",Year(Date())-Year([People].[Brithdate])+1,[Peopl
e].[Brithdate]),DateAdd("yyyy",Year(Date())-Year([People].[Brithdate]),[Peop
le].[Brithdate]));
Please post back if it still doesn't work (or even if it does
)
Debra
Rli said:
Hi Debra, thank you for 'jumping in'.
But does your query give the birthday 15/01/2006 on systemdate 16 december
2005 when someone was born on 15/01/1960 ???
I dont think so because it will test on birthday 15/01/2005 and not 15/01/2006
you see what i mean?
Debra Farnham said:
Sorry to jump in here, but I've been watching the thread for a bit out of
interest and have found that this works:
SELECT People.Birthdate,
DateAdd("yyyy",Year(Date())-Year([People].[Birthdate]),[People].[Birthdate])
AS Birthday
FROM People WHERE (((People.Birthdate) Is Not Null) AND
((DateAdd("yyyy",Year(Date())-Year([People].[Birthdate]),[People].[Birthdate
])) Between Date() And DateAdd("m",1,Date())));
HTH
Debra
Yes....that helps...it actually does not give the error anymore
But it does not give me the right birthdays coming for the next month
:
Do you have any rows in your table where birthdate is null?
I just ran the exact query as a test, and I got the same error message
as
you for Null dates. Adding "AND People.Birthdate IS NOT NULL" solved it
for
me...
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Yes, thought it should, but it does give an error: for instance
SELECT people.birthdate,
DateAdd("yyyy",Year(Now())-Year(people.birthdate)+1,people.birthdate)
AS
birthday
FROM people
WHERE
DateAdd("yyyy",Year(Now())-Year(people.birthdate)+1,people.birthdate)
Between now() And DateAdd("m",1,NOW());
gives the error (sorry, in french...) "Type de données incompatible
dans
l'expression du critère" (in short...incompatible types in the
criteria,
Error 3464)
OR no records at all......
When i remove the WHERE part it works fine and gives birthday for all
records.
But as soon as i use "Dateadd.... between .. and.." as a criteria it
fails
for incompatible types
[Birthdate] is a date field....and i did not create any of the objects
you
mention.
can you see why?
But still even if this does work, it would not give me all the
brithdays
for
next month...
So the initial question how to retrieve birthdays still stands....
:
Assuming [birthday] is a date field (as opposed to a text field),
that
should work.
One thing that could be causing a problem is if you've created any
objects
(variables, controls on forms, fields in tables, etc.) named Year,
Month
or
Day.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
i tried:
WHERE DateSerial(Year(now()),Month([birthday]),Day([birthday]))
BETWEEN
now() AND DateAdd("m",1,now())
but that gave me an error of incompatible types and
it will not work for birthday 10 january 1950 when it is now 15
december
2005
so what to do??? can anyone help?
:
thanks for your reply, but that does not seem to work....
it gives me only dates that are between date() and date()+31 not
anniversaries of dates (by the way...some months have 28,30 days
not
31)
so any other suggestions?
:
In your query, enter the following criteria in your birthdate
column
between date() and date()+31 This will give you the dates for
the
next
31
days.
--
Allan Murphy
Email: (e-mail address removed)
I have a table with birthdate of persons
I would like to write a query giving me all anniveraries
coming up
within
one month from now.
Can someone help me with this?
