This month, Peter Vogel looks at a problem in managing
dates and displaying information using conditional
formatting. He starts with a solution to the problem, but uses
that as a springboard to discuss what processing should be
done in the different parts of your application.
THIS month’s column addresses a single question in
two parts. The first part of the question revolves
around how to retrieve the data, while the second
part discusses how to display the result. My answer here
is almost certainly overkill. What I want to demonstrate in
this column are the kinds of issues that you should be
considering when answering even the simplest problems.
My problem is that I’ve got a table with a set of scheduled
activities. Each table has a startDate field and an endDate
field. What I want to do is find every activity that either
begins or ends within a specific date range. In addition,
sometimes the date range that I’m searching in is only one
day long. Also, sometimes my scheduled activities are only a
day long. I need to flag those activities that either start or
end outside the date range.
I’m going to give you a SQL statement that will retrieve
the data that you need. You can do the following:
• Use the statement in a query that’s the RecordSource
for a form or report.
• Insert the resulting data into a table and display it in a
form or report.
• Set a form’s Recordset property equal to the recordset
returned by the SQL statement (if you’re using Access
Once the data is returned, I’m going to use formsbased
processing to handle displaying the data. In other
words, I’m distinguishing between the business/datarelated
activities (the SQL statement) and the presentation
The first step in the SQL statement might be to
look for all activities that either begin or end within the
period. So, assuming that 01/01/2001 and 12/31/2001
are your test dates, you might write a SQL statement
like this: ......
Read more in the pdf article here