Link to home
Start Free TrialLog in
Avatar of contact9
contact9

asked on

sorting records bwtween 2 dates?

hi,
    can someone tell how to write a sql query for sorting records between 2 dates in vb....
           
Avatar of Mayank S
Mayank S
Flag of India image

Why not use the ORDER BY clause?
Avatar of FrankTheRat
FrankTheRat

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

Avatar of contact9

ASKER

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.......
sql = "SELECT * FROM TableName " _
& "WHERE (DateFieldName > '" & format(InputDate1,"dd-mm-yyyy") & "' AND DateFieldName < '" format(InputDate2,"dd-mm-yyyy") & "') order by DateFieldName"

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
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.
     
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
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.
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
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...
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





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....

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....
thanks i got it
ASKER CERTIFIED SOLUTION
Avatar of spongie
spongie
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial