Wrox Programmer Forums
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 26th, 2004, 04:56 PM
Authorized User
 
Join Date: Jun 2003
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to hcweb
Default Finding oldest to newest dates

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.
 
Old January 26th, 2004, 05:05 PM
Authorized User
 
Join Date: Dec 2003
Posts: 81
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old January 26th, 2004, 05:08 PM
Authorized User
 
Join Date: Jun 2003
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to hcweb
Default

Nearest and furthest.
 
Old January 26th, 2004, 05:14 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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.
 
Old January 26th, 2004, 05:14 PM
Authorized User
 
Join Date: Jun 2003
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to hcweb
Default

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.
 
Old January 26th, 2004, 06:44 PM
Authorized User
 
Join Date: Dec 2003
Posts: 81
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old February 23rd, 2004, 03:12 AM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 171
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

 
Old February 23rd, 2004, 03:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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.
======================================
 
Old February 23rd, 2004, 11:14 AM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 171
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Select Newest entry Lofa SQL Server 2000 1 February 22nd, 2006 07:56 AM
Returning the newest date in CR? johnyo101 Crystal Reports 0 November 18th, 2004 06:31 PM
Newest Topic/Post by: [MemberID] WOPR BOOK: ASP.NET Website Programming Problem-Design-Solution 7 September 5th, 2004 02:26 AM
Finding the Owner bmains SQL Server 2000 5 August 3rd, 2004 08:42 AM
Finding the maximum and using it outside the IF jrwarwick XSLT 2 October 17th, 2003 03:59 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.