Querying dates with grids from a database

[colin]

Hi,

I am trying to display information in a grid which is being pulled from a database. However I want be be able to prompt the user to input the date they want to display when the form opens. I have two dates to search from, [to_date] and [from_date] and would like to do the equivalent of a "Between [to_date] and [from_date]" like you can do in access to open all records which fall inbetween those dates. I would be most gratfull for help to any part of this problem.

Many Thanks

[Gaffer]

I suspect you are using VB as the front end and Access97 as the database. On that premise, there are a multitude of ways of selecting 2 dates. Unfortunately, I know of no way of emulating Access’s ability to allow the user to input a parameter based on the fact that the underlying Access-SQL query includes [Field] to envoke the dialog box.

The solution I would use for this is two text boxes on the form (eg txtToDate, txtFromDate). The added advantage of using this method is that you can ensure that the date is validated, that the to_date is not less than the from_date etc etc ie you have more control.

After this, you can create your recordset like this:

Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase("c:\test.mdb")
Set rs = db.OpenRecordset("select * from tblTest where Date Between ‘” & txtFrom_date & “’ And ‘” & txtTo_date & “’")

Hope this helps…