?
Solved

sorting records bwtween 2 dates?

Posted on 2003-03-31
15
Medium Priority
?
226 Views
Last Modified: 2010-04-17
hi,
    can someone tell how to write a sql query for sorting records between 2 dates in vb....
           
0
Comment
Question by:contact9
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 30

Expert Comment

by:Mayank S
ID: 8238086
Why not use the ORDER BY clause?
0
 
LVL 1

Expert Comment

by:FrankTheRat
ID: 8238100
I don't know about the VB bit the the SQL should look something like:

SELECT * FROM TableName WHERE (DateFieldName > '01-Jun-1999' AND DateFieldName < '01-Dec-1999').

Hope this helps.

FrankTheRat

0
 

Author Comment

by:contact9
ID: 8238122
hey franktherat the dates r not fixed and they r entered by user,,,,,,the dates r taken from user and then the records are sorted.......
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 53

Expert Comment

by:Dhaest
ID: 8238318
sql = "SELECT * FROM TableName " _
& "WHERE (DateFieldName > '" & format(InputDate1,"dd-mm-yyyy") & "' AND DateFieldName < '" format(InputDate2,"dd-mm-yyyy") & "') order by DateFieldName"

0
 
LVL 1

Expert Comment

by:FrankTheRat
ID: 8240452
My application takes the date values from a couple of DateTime controls and also allows sorting by any table field. The application presents all available fields in a drop-down combobox and the

For example (this is not syntactically accurate, but you get the idea):
SELECT * FROM TableName WHERE (DateFieldName > DateCTRL1.Date AND DateFieldName < DateCTRL2.Date) ORDERBY FieldComboCTRL1.Text).

Others may have more comprehensive advice if you let us know what language you're using and what sort of application architecture you're using.

FrankTheRat
0
 

Author Comment

by:contact9
ID: 8261195
To franktherat,
              see i am using vb as front end and sql as backend,...i have taken 2 text boxes and a datagrid,,,,
the user should enter the start date in frist text box and end date a tthe secondtext box,,the result must be displayed in the datagrid.
     
0
 
LVL 1

Expert Comment

by:FrankTheRat
ID: 8263561
Sorry, I don't know VB.

Delphi has non-visual controls that connect to databases (e.g. TQuery) so that the developer can build a query string from your visual controls (eg Text controls) and submit an SQL query to the database.

To make life easier, there are even 'data aware' controls that connect to database fields.

If VB has a DateTime control, this would be better than a plain text control as you won't have to parse the input yourself.

In my example above, DateCTRL1.Date provides access to a DateTime formated value from a standard Delphi control of class TDateTimePicker, which can just be inserted in the SQL query string.

Sorry I can't offer more VB related help.

Good luck.

FrankTheRat
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 8267011
You can use the DateDiff () function in VB to compare 2 dates:

Dim d1 As Date
Dim d2 As Date
Dim diff As Long
..
.. ' read values
..
..
diff = DateDiff ( "d", d1, d2 ) ' "d" stands for 'date' - you can also find the difference for months, years, hours, etc

If diff = 0 Then
  ' same date
Else
  If diff > 0 Then
    ' d2 comes after d1
  Else
    ' d1 comes after d2
  End If
End If

You can use the CDate () function to convert a string to a date as:

Dim d As Date
Dim str As String

str = "May 25, 1982"
d = CDate (str)


Hope that helps!

Mayank.
0
 

Author Comment

by:contact9
ID: 8274674
hello dahest
          i am using sorting out this result in data grid,,,,,
but when i fire this query on a command button,,,,,,nothing happens,,,,,,,any solution,,,,,
                              ujjwal
0
 
LVL 8

Expert Comment

by:spongie
ID: 8283199
Are trying to only get only the records that fall between two dates? if so try this...

Dim startDate as date
Dim endDate as date
Dim strSQL as string

startDate = Format(textbox1.text,"Short Date")
endDate = Format(textbox2.text, "Short Date")

strSQL = "Select * from [tablename] where [datefield] > #" & _
   DateAdd("d",-1,startDate) & "# And [datefield] < #" & _
    DateAdd("d",1,endDate) & "# Order by [datefield] ASC"

...run strSQL. This would work even when the date format in your database includes hours, minutes, and seconds. Sometimes, you don't get the desired results because of these hours and minutes.  Dates that are equal to the end and start date will also be returned in the query.  Hope this helps...
0
 

Author Comment

by:contact9
ID: 8283439
hi spongie,
           see i am viewing this records in a datagrid,,,,

i have taken a datagrid two text boxes to accept the 2 dates..
this is the code for it,.......

 'On Error Resume Next:
Dim sortrs As New Recordset
Dim strsql As String
 strsql = "SELECT certno,dtofrec from pharma where dtofrec>" & CDate(Text1.Text) & " and dtofrec< " & CDate(Text2.Text) & "order by dtofrec"
 Set DataGrid1.DataSource = sortrs
 sortrs.Open strsql, con, adOpenStatic
DataGrid1.Width = 5000

'DataGrid1.Columns(2).Width = 4000
    With DataGrid1
'
         Set .DataSource = sortrs
                DataGrid1.Columns(0).Width = 4000
               DataGrid1.Columns(1).Width = 4000
         
        .ClearFields
     .ReBind
 End With
sortrs.close

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
bur when i click on command button only trhe headings of cloumn are see not the sorted records.,,,,,,,,,,,
                        c u help me,,,,,,in this regard..

                                                   ujjwal




0
 
LVL 8

Expert Comment

by:spongie
ID: 8292394

Try this...



Set sortrs = New Recordset
Dim strsql As String
strsql = "SELECT certno,dtofrec from pharma where dtofrec > #" & CDate(Text1.Text) & "# and dtofrec < #" & CDate(Text2.Text) & "# order by dtofrec"
sortrs.Open strsql, con, adOpenStatic, adLockReadOnly
DataGrid1.Width = 5000


   With DataGrid1

        Set .DataSource = sortrs
               DataGrid1.Columns(0).Width = 4000
              DataGrid1.Columns(1).Width = 4000
       
       .ClearFields
       .ReBind
   
End With


...see the difference? no 'sortrs.close' (closing the recordset would also close the datasource) and the '#' signs. This query wouldn't include the ones that are equal to the beginning and end dates though. try including the codes i posted previously.

hope this solves your problem....
0
 
LVL 8

Expert Comment

by:spongie
ID: 8292397

Try this...



Set sortrs = New Recordset
Dim strsql As String
strsql = "SELECT certno,dtofrec from pharma where dtofrec > #" & CDate(Text1.Text) & "# and dtofrec < #" & CDate(Text2.Text) & "# order by dtofrec"
sortrs.Open strsql, con, adOpenStatic, adLockReadOnly
DataGrid1.Width = 5000


   With DataGrid1

        Set .DataSource = sortrs
               DataGrid1.Columns(0).Width = 4000
              DataGrid1.Columns(1).Width = 4000
       
       .ClearFields
       .ReBind
   
End With


...see the difference? no 'sortrs.close' (closing the recordset would also close the datasource) and the '#' signs. This query wouldn't include the ones that are equal to the beginning and end dates though. try including the codes i posted previously.

hope this solves your problem....
0
 

Author Comment

by:contact9
ID: 8299291
thanks i got it
0
 
LVL 8

Accepted Solution

by:
spongie earned 150 total points
ID: 8300912
Good. Glad to hear that. Just click the answer as accepted though. Thanks.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Why WooCommerce is one of the majorly favored choices when it comes to having an eCommerce store. This article will acquaint you with some reasons that I believe make it one of the best eCommerce platforms available.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Starting up a Project

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question