Hello!

I would like a countdown to Christmas field for a table.  I subtract two ds(dates) (but when I get past 12/25 I want to subtract from the following year.  I tried an iif expression but received errors.  Am only looking to be pointed in the right direction.

Thank you!!!!!
Intermediate Access User

---

Answer from Richard Rost:

This is probably going to be a pretty complex IIF function. You're better off writing it as a global VBA module. Something like this:

[This is pseudocode, I haven't tested it]

public function XmasCountdown() as integer
  if month(date()) = 12 then
    ' december
    if day(date()) > 25 then
       ' calculate days til next xmas here
    else
       ' calc days til the 25th here
       ' this one's easy:
       X = 25 - day(date())
    end if
  else
      ' not december
      ' calc days til xmas here
      ' a simple datediff should work
   end if
  XMasCountdown = X
end function


I hope this is good enough to get you started. If you need more help, let me know. There are a whole ton of date-related functions in Access. For help on some of them, like DateDiff, see this tutorial:

There are many different things you can do with dates and times in Access.

You can use the DateAdd function to add or subtract date values. Want to add 6 months to a date but aren't sure which months between have 28 or 31 days? DateAdd takes care of that. It's also good for time values.

Please see the following FREE video tutorial on my web site for step-by-step instructions on how to use the DateAdd function:

http://599cd.com/tips/access/number-of-months-alive/?key=BlFAlEx


You can also use the DateDiff function to calculate the difference between two dates or times. This is useful for calculating the number of hours worked, someone's age, or even the number of school days in a year.

Here's a FREE video tutorial that discusses how to do some of that:

http://599cd.com/tips/access/dates-datediff-dateadd/?key=BlFAlEx


Now, one thing I get asked about ALL the time is how to figure out the number of days someone has to work, but EXCLUDE holidays like Christmas or Thanksgiving. Well, this is not so easy because some holidays (like Thanksgiving) fall on different calendar dates from year to year. In order to do this, you need to create a lookup table for your holidays, and use some advanced programming.

Unfortunately, I don't have a free tutorial that explains this in detail, but I do cover it in my Access 325 course, in lesson 4. We build a table to track student attendence, and we use a Holiday Table to know which days the students don't have to be in class. Here's the link:

http://599cd.com/site/courselist/access/access325/?key=BlFAlEx


I hope this helps.