Dates, Data Access and Presentation

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Jan-2003 >

Dates, Data Access and Presentation

Peter Vogel

 

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

2000/2002).

 

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

layer activities.

 

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 Dates, Data Access, and Presentation