Hello,
All
I have make table & form in MS Access and have two text field one is from------ and other is to----- and I want to search data Date wise for example 15/10/2000 to 15/10/20007 and press search button but no result found.
please help me to write query.

thanks regards


IrshadAhmad

It is helpful if there are actual field and table names, and the format of the query results you want to display. If you are using text boxes to allow users to type in from and to dates, you will need to do some data validation as part of your code. Another way to do this is to create the query using the query designer, and then add this line to the date field criteria:

Between #[Please enter a start date:]# And #[Please enter an end date:]#

Then use the button wizard to create the button to launch the query, and the query will prompt the user for these parameters, and then display the results in a datasheet.

If you were using a form or report to launch the query results, you can create the same sort of query, but in the Criteria line for the date field you can do this:

Between #[Forms]![frmMyForm].[txtMyStartDateTextBox]# And #[Forms]![frmMyForm].[txtMyEndDateTextBox]#

The form or report will launch, and the query will look for the parameters for you.

Another way to do this, and my preferred method since it uses data vaidation, is:

Dim dtStart As Date
Dim dtEnd As Date
Dim sLink As String
Dim sDoc As String

If IsNull(Me.txtStartDate) Or Me.txtStartDate = "" Then
   MsgBox "Please enter a start date", vbCritical
   Exit Sub
Else
   dtStart = Me.txtStartDate
End If
If IsNull(Me.txtEndDate) Or Me.txtEndDate = "" Then
   MsgBox "Please enter an end date", vbCritical
   Exit Sub
Else
   dtEnd = Me.txtEndDate
End If

sLink = "[DateField] Between #" & dtStart & "# And #" & dtEnd & "#"

sDoc = "frmMyFormName"

DoCmd.OpenForm sDoc, , , sLink

You would need to add some validation to make sure the values are dates, otherwise if they are not, errors will be thrown.

I use combo boxes and have them look up distinct entries in the date field you will be using to make sure users can only select valid dates. I also use cascading combo boxes so that when the user selects a start date, they can then only choose end dates that are after the start date, otherwise you need to validate that as well, which I left out above.

Did that help?



mmcdonal

Look it up at: http://wrox.books24x7.com