|
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.
|
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. |
|
