finding next record by date.. question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

finding next record by date.. question

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2004-10-07 : 11:15:54

Hey all! First post here..

Here is my background: I have a dataGrid in vb.NET that is populated by a SQL statement that selects records by DATE (from user input in vb). Now... I want to add a 'Next Date' button that will run a SQL statement to find the next DATE and then display the records, and then repeat.

My current string to get the first set of records from the user inputted DATE is here:

Dim strTimeByDate As String = "SELECT Hours.Datewrk, Employee.Lastname, Employee.Firstname, Employee.EmployNo, Hours.Hourswrk, Hours.typewrk, Hours.formwrk, Hours.class, Hours.brate, PurchaseOrder.Descr, PurchaseOrder.Purchord, Hours.TicketNo FROM Hours As Hours INNER JOIN PurchaseOrder As PurchaseOrder ON Hours.Purchord = PurchaseOrder.Purchord INNER JOIN Employee As Employee ON Hours.EmployNo = Employee.EmployNo WHERE Hours.Datewrk = '" & txtDate1.Text & "' And PurchaseOrder.JobNo = '" & cboJobNo1.Value & "' ORDER BY Employee.Lastname, Employee.Firstname"

Now how do I structure a query to just go down the row of DATES and get the next available one? Soo I should be able to view records from 6/14/04 then hit the button, and it finds the next records are from 6/18/04, and so on. Thank you all for any guidance!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-07 : 11:21:41

include a where clause?

hueby
Posting Yak Master

127 Posts

Posted - 2004-10-07 : 11:31:26

Okay, so.. (WHERE Hours.Datewrk = '" & txtDate1.Text & "') should be structured somehow to, WHERE Hours.Datewrk = "next record by date worked" How do I go about telling it to do that?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-07 : 11:36:00

add some line breaks and formatting to your first post, please. the whole thread is streched out.

- Jeff

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-07 : 11:43:57

Well you can select all the records where date>=criteriaDate order by date
and show only the ones with the criteria date and when the button is clicked then
you change the criteriaDate to the next date and display records with that new date. Forget it

I would create a disconnected recordset with all available dates
ordered and grouped by date and position the cursor in the selected date (myRec.find)
when the next button is clicked then move myRec cursor to next record (myRec.moveNext)
and use the date in current record to make the new search.

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

hueby
Posting Yak Master

127 Posts

Posted - 2004-10-07 : 12:10:06

ohhhhh Okay! That makes sense Tuenty. Thanks for that adivce using the diconnected recordset.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources