The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
March 2007

Date Math 103 – DateAdd on Steroids

During past months we’ve focused on the subject of date math. We learned how dates are stored and how to use the DateAdd Function to do straightforward addition and subtraction of dates and times.

Here are two scenarios to kick off this month’s exploration of date math: let’s say that we need the first day of next month or the last day of this month. Is this something that the DateAdd function can handle? The answer is yes-given a solid grasp of how the DateAdd function works and a bit of ingenuity, we can handle just those scenarios with nothing more than the DateAdd function.

In this Issue
  • Tip of the Month – Associating a label with a control
  • First Day of Next Month
  • Last Day of Month – The Lazy Programmer’s Approach
  • Conclusion and a Look Ahead
  • Trap of the Month – Don’t confuse the Functions Cint And Int

  • First Day of Next Month

    What if we wanted to find the first day of the month after March 17, 2007? (Erin Go Bragh!) We’ll start by using the DateAdd function to add one month: DateAdd("m", 1, #3/17/07#). In this case, the output of this function is April 17, 2007. If we could just isolate the value for the day and subtract that (value–1), we’d have our answer.

    It turns out there’s a function called Day that will help us with this. The Day function has only one argument - date, and it returns the day of the month of the date entered into the function. Building on the DateAdd function above by adding on the Day function, the code looks like this:

    Day(DateAdd("m", 1, #3/17/07#))

    The value returned is 17.

    If we want the first day of next month, all we have to do is use the DateAdd function to determine the date one month out and then subtract the value from the Day function with that date as the input. To get to the first of the month, we’d add the value of 1 to the result.

    So the code string looks like this:
    DateAdd("m", 1, #3/17/07#) - Day(DateAdd ("m", 1, #3/17/07#)) +1

    This returns April 1, 2007.

    Last Day of Month – The Lazy Programmer’s Approach

    Don’t think too hard about this; you can make it a whole lot more complex than it really is.

    The initial approach may involve invoking the children’s rhyme “30 days hath September” to figure out how many days there are in the month and then determining how many days to add to reach the last day. But this approach is needlessly complex.

    Since we’ve just reviewed how to get to the first day of the following month, all we have to do is subtract 1 from that value. Alternately since we had to add one to get the first day of the next month, simply omitting the + 1 we get the answer straight away!

    In other words its:
    DateAdd("m", 1, #3/17/07#) - Day(DateAdd ("m", 1, #3/17/07#)), just like above except we’ve omitted the “-1”.

    Conclusion and a Look Ahead

    This month we looked at how to use the DateAdd function to get values for the beginning of a month and the end of a month. By using functions and applying just a bit of logic, we figured out how to simplify a potentially complex problem.

    Next month we’ll dig even deeper into date math and look at another date function that is truly powerful for figuring out dates.

    Trap of the Month – Don’t confuse the Functions Cint And Int

    A few issues ago, I talked about a function called Int. I mentioned that this function essentially returns the integer portion of a number. There’s a very similar function in Access called Cint (as in Convert to Integer). The Cint function appears to work the same as Int, except that where Int will return the integer portion of a number, Cint will round a given number either up or down before returning the integer.

    Beware the difference between these two functions and make sure you’re using the one that fits your needs.

    Tip of the Month – Associating a label with a control

    You probably know that when you’re designing a form or report and you place a textbox (or other controls such as checkbox or combo box), on your design surface, it comes with a label that moves along with it.

    Sometimes you delete the label intentionally because you no longer want or need it. But sometimes you might delete it accidentally or disconnect the text box and the label so that it doesn’t move when you move the control because it no longer is associated with the textbox. How do you connect them back together?

    Like many things, it’s really easy to create a relationship between a control and a label if you know the trick.

    • First, put a new label on your design surface, then cut the label from the form or report.
    • Select the control that you want to associate the label with.
    • Once selected, paste the label back onto the design surface and the new label will be associated with your control.

    This works not only with textboxes, but any control that normally has a label associated with it.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz

    Custom Software | © Copyright Custom Software All Rights Reserved | Westford | MA | 01886