Number of days to birthday

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

This should be really simple but I hate working with dates and it's driving
me crazy.

I have a query that is supposed to have a calculated field that show the
number of days to a person's birthday but I cannot get it to work correctly.

Can anyone put me out of my misery on this one and post an example?

Andy
 
This should be really simple but I hate working with dates and it's driving
me crazy.

I have a query that is supposed to have a calculated field that show the
number of days to a person's birthday but I cannot get it to work correctly.
Click to expand...

The DateDiff function will do this. If you're using the query builder interface, your calculated column would look
something like this:

DaysToBD:datediff("d",Now, [Birthday])

Assuming you have a field named Birthday in your table, your query would then show a column named DaysToBD with the info
in it ...

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com
 
Scott McDaniel said:
This should be really simple but I hate working with dates and it's
driving
me crazy.

I have a query that is supposed to have a calculated field that show the
number of days to a person's birthday but I cannot get it to work
correctly.
Click to expand...

The DateDiff function will do this. If you're using the query builder
interface, your calculated column would look
something like this:

DaysToBD:datediff("d",Now, [Birthday])

Assuming you have a field named Birthday in your table, your query would
then show a column named DaysToBD with the info
in it ...
Click to expand...

Presumably Birthday contains the actual date of birth. Your calculation
needs to know when the birthday will be this year (or next year, if it's
already been this year):

DaysToBD: IIf(Format([Birthday], "mmdd") < Format(Date, "mmdd"),
DateDiff("d", Date, DateSerial(Year(Date) + 1, Month([Birthday]),
Day([Birthday]))), DateDiff("d", Date, DateSerial(Year(Date),
Month([Birthday]), Day([Birthday]))))

(all on one line, of course)
 
Douglas J. Steele said:
DaysToBD: IIf(Format([Birthday], "mmdd") < Format(Date, "mmdd"),
DateDiff("d", Date, DateSerial(Year(Date) + 1, Month([Birthday]),
Day([Birthday]))), DateDiff("d", Date, DateSerial(Year(Date),
Month([Birthday]), Day([Birthday]))))
Click to expand...

Wow, not quite as straightforward as I imagined, no wonder I was having
trouble.

Many thanks

Andy
 
Douglas J. Steele said:
DaysToBD: IIf(Format([Birthday], "mmdd") < Format(Date, "mmdd"),
DateDiff("d", Date, DateSerial(Year(Date) + 1, Month([Birthday]),
Day([Birthday]))), DateDiff("d", Date, DateSerial(Year(Date),
Month([Birthday]), Day([Birthday]))))
Click to expand...

What data type is this is? Is it variant Long?
If I try to put a '<30' limit to find birthdays within a month I get a type
mismatch. It is causing some problems if I try to filter on it.

Thanks again

Andy
 
Yeah, it should be a variant (Long).

While it shouldn't be necessary, you could try:

DaysToBD: CLng(IIf(Format([Birthday], "mmdd") < Format(Date, "mmdd"),
DateDiff("d", Date, DateSerial(Year(Date) + 1, Month([Birthday]),
Day([Birthday]))), DateDiff("d", Date, DateSerial(Year(Date),
Month([Birthday]), Day([Birthday])))))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Andy said:
Douglas J. Steele said:
DaysToBD: IIf(Format([Birthday], "mmdd") < Format(Date, "mmdd"),
DateDiff("d", Date, DateSerial(Year(Date) + 1, Month([Birthday]),
Day([Birthday]))), DateDiff("d", Date, DateSerial(Year(Date),
Month([Birthday]), Day([Birthday]))))
Click to expand...

What data type is this is? Is it variant Long?
If I try to put a '<30' limit to find birthdays within a month I get a
type mismatch. It is causing some problems if I try to filter on it.

Thanks again

Andy
Click to expand...
 
Back
Top