I'm querying a database that holds court schedule. When a user enters a court schedules, they have to enter a court date. This database is cleared and reloaded on a regular basis based on completed schedules.

I would like to be able to query the DB without knowing any of the dates, and extract dates for a "from/To" display such as:

Court Schedule runs 1/26/2004 through 2/25/2004

I tried objRS.MoveFirst and objRS.Movelast but of course, that only gives me the first and last entered record which would not give me the actual dates that I'm looking for.

Does anyone know of a way to move through the records, extract one starting date (i.e. 1/26/2004) and then find the future ending date (i.e. 2/25/2004) without knowing what possible date range is there?

Any help would be greatly appreciated.

ARE YOU LOOKING TO QUERY THE DATA BASED ON A USER-INPUT RANGE OF DATE OR ARE YOU LOOKING FOR THE DATE NEAREST AND FURTHEST FROM THE CURRENT DATE??

JOHN

Nearest and furthest.

You can loop thru the recordset and compare the date of the record to a saved startDate and endDate. Test whether the date is less or greater than the respective stored date. This way you can get the oldest and newest date.

Dim dtmStartDate
Dim dtmEndDate
If Not(objRS.BOF And objRS.Eof) Then
    dtmStartDate = CDate(objRS("datefield"))
    dtmEndDate = CDate(objRS("datefield"))
    objRS.MoveNext()
    While Not(objRS.BOF And objRS.Eof)
        If CDate(objRS("datefield")) < dtmStartDate Then
            dtmStartDate = CDate(objRS("datefield"))
        End If
        If CDate(objRS("datefield")) > dtmEndDate Then
            dtmEndDate = CDate(objRS("datefield"))
        End If
        objRS.MoveNext()
    End While
End If

Alternatively, you could have a single database query return you just those two bits of information:

sql = "SELECT MIN(datefield), MAX(datefield) FROM tablename"
...
dtmStartDate = CDate(objRS(0))
dtmEndDate = CDate(objRS(1))


Peter
------------------------------------------------------
Work smarter, not harder.

Actually, the way the scheduling goes, a current set of court cases could be scheduled as far as a month in advance with no current cases prior to that. That leaves me the unknown of what dates are actually in the DB.

If interested, the actual working page is located here:
http://www.co.harrison.ms.us/dockets/chancery/index.asp

The section at the top of the page is what I'm referring to.

By looping through the recordset, as Peter demonstrated, it wouldn't matter what the dates are. If you assume that any dates in the dB will be today or in the future the loop will give you the appropriate information. If a date prior to today is accceptable that will trace also. If it is not you can write a conditional statement that sets the lowest range date to today after the loop is run.

John

Give this a shot.

    MinDate = cDate("12/31/3000")
    MaxDate = cDate(0)

    set rs = server.createobject("adodb.recordset")
    sql = "select min(DateField), max(DateField) from Schedules"
    rs.open sql, cn
    if not rs.eof then
        for i = 0 to rs.fields.count - 1
            if rs(i) < MinDate then MinDate = rs(i)
            if rs(i) > MaxDate then MaxDate = rs(i)
        next
    response.write "Court Schedule run " & MinDate & " through " & MaxDate
    end if
    rs.close
    set rs = nothing

Dave Gerard,

It is great that you have an alternative answers to many of these questions.

However, you seem to be answering many questions in succession that are months old.

I would hope that the people with the queries would have moved on or posted again by now.

======================================
They say, best men are moulded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

Sorry, I really didn't check the date. I thought that perhaps, even if the person found a solution, someone else might run across this post and find some value in it.