Filtering Records

[Zvonko]

Hello!

I need some help with SQL statements. My question is:
How can I retrieve from my DB those records that were last accessed between two dates, let's say that I have to retrive all records from 2. june 2000 to 1. october 2000.
Does anyone know how?

[monte96]

Are you storing the dates they were accessed on in the database?

[HunterMcCray]

Originally posted by Zvonko
Hello!

I need some help with SQL statements. My question is:
How can I retrieve from my DB those records that were last accessed between two dates, let's say that I have to retrive all records from 2. june 2000 to 1. october 2000.
Does anyone know how?

Assuming that you are using ACCESS and that your table includes a field of the Type DATE/TIME and that you UPDATE that field every time you access a record:

strSQL="SELECT TableName.* FROM TableName WHERE (((TableName.DateField) > #6/2/2000#) AND ((TableName.DateField) < #10/2/2000#)) ORDER BY TableName.DateField;"

Notice that the second date is one day greater than your target date. This is true because dates are stored as numbers with the decimal place being the time. If a day begins with say 345,678 and you want to in clude that day in your recordset then you need to test for 345,678.9999999 as well as any other decimal of 345,678. Because users do not think of the exclusive nature of an ending date I find it best to use code to make sure that there is no time portion of a date input by users and then add one day for the SQL statement ensuring that the day the user enters is included in the recordset.

If you do not have a date field in your table then there is no resolution to your problem. The overhead incurred in creating a date field for every record in a table and automatically updating it every time it was accessed would bring any large database to a creeping halt for no particular purpose other than "snooping" in a multi-user environment. (in a poorly constructed version the act of updating the access date could trigger a do loop of updating the table because the access date was updated...) If you are working with date sensitive data then you should pay particular attention to the way that you write your front-end, carefully creating classes that handle record creation, record viewing, and record updating, and carefully deciding which actions are important to note. For instance in a sales program it might only be important to note the dates that a change were made to an item's price or description, but not when the record was viewed. In the invoice table it might be important to know when an invoice was viewed by a certain user....Avoiding large accumulations of unimportant data is just as important as saving data that is important. Never assume that a program that you are writing knows where you want it to go; you have to tell it in, often, excruciating detail.

Hope that this helps,

Hunter