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....
can someone tell how to write a sql query for sorting records between 2 dates in vb....
Why not use the ORDER BY clause?
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
SELECT * FROM TableName WHERE (DateFieldName > '01-Jun-1999' AND DateFieldName < '01-Dec-1999').
Hope this helps.
FrankTheRat
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-y yyy") & "' AND DateFieldName < '" format(InputDate2,"dd-mm-y yyy") & "') order by DateFieldName"
& "WHERE (DateFieldName > '" & format(InputDate1,"dd-mm-y
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
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
ASKER
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.
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
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.
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.
ASKER
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
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,"Shor t 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...
Dim startDate as date
Dim endDate as date
Dim strSQL as string
startDate = Format(textbox1.text,"Shor
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...
ASKER
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).Widt h = 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
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).Widt
With DataGrid1
'
Set .DataSource = sortrs
DataGrid1.Columns(0).Width
DataGrid1.Columns(1).Width
.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
DataGrid1.Columns(1).Width
.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
DataGrid1.Columns(1).Width
.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....
ASKER
thanks i got it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.