Search a databse using date values

[shooteronline]

Hi,

I have vb form whitch contain two text boxes.

First one is Begindate
Second one is Enddate

If I want search a databse using begin date and end date how do i create sql statment.And also i want to display record in grid.
How to do that? Pl explain in simple english in details steps.


Thankx

shooter

[abhijit]

Hi,
Use the between clause to search records between two date values.

For e.g:-

Use Northwind
go
SELECT * FROM [ORDERS] WHERE ORDERDATE BETWEEN '1996.07.06' AND '1996.07.10'	
go

For populating a grid. Open a recordset with your query. And set the datasource property of the DBGrid to the recordset. That should do the trick.

Cheers!
Abhijit

[johny p]

You can make a query using a Sql string. You have to use format #Month/Day/year# in the sql String.

‘For example how to search between two dates begin an end.

Dim Sql as string

‘Me.txtBeginDate = Textbox with first Date
‘Me.txtEndDate = Textbox with end Date


Sql = “Select * from tbl... where Date(=field from tbl...) BETWEEN #" & Month(Me.txtBeginDate) & "/" & Day(Me.txtBeginDate) & "/" & year(Me.txtBeginDate) & "# And #" & Month(Me.txtEndDate) & "/" & Day(Me.txtEndDate) & "/" & year(Me.txtEndDate) & "#)”


I hope this will help You.

[Pasvorto]

or...

SELECT * FROM <table> where [date field] >= " & "#" & cdate(txtstartdate.text) & "#" & " and [datefield <= " & "#" and cdate(txtenddate.text) & "#"

[abhijit]

Originally posted by Pasvorto
or...

SELECT * FROM <table> where [date field] >= " & "#" & cdate(txtstartdate.text) & "#" & " and [datefield <= " & "#" and cdate(txtenddate.text) & "#"

Pasvorto,
This appears to be a query written for access. I am using one for SQL Server.

Cheers!
Abhijit

[techgnome]

Here's two ways we do it:

Dim strSQL As String
strSQL = "SELECT blah FROM tblFoo WHERE DateField BETWEEN '" & Format$(CDate(BeginDate.Text), "mm/dd/yyyy") & "' AND '" & Format$(CDate(EndDate.Text), "mm/dd/yyyy") & "'"
 
Set rs = objCmd.Execute strSQL