Dates search is driving me Crazy!

[Maz100]

Hello!

Hope someone can help. My code behind my command button is not working. It is opening Report1 but not the criteria in the query.

I have a form (form1) with 2 unbound text boxes on, called "txtDatefrm" and "txtDateto" in which dates can be entered. There is also a command button on the form which searches for these dates and then opens a report(Report1). The report's control source is a query. (query1). On the ExpiryDate field in the query I have the criteria:
Between ([forms]![form1]![txtDateFrm]) and ([forms]![form1]![txtDateTo])

Behind the command button I have the code:
Dim stdocname as string
Dim ExpiryDate as Date
txtDateFrm.setfocus
ExpiryDate = txtDateFrm.text
If Trim(ExpiryDate) <>"" Then
stdocname = "Report1"
Docmd.Openreport stdocname,acViewPreview
End if

Many Thanks for any suggestions!

 

[KenHigg]

You should simply try get the report to run with two valid dates in the text boxes to start with. Once you get that to work, then figure out the best way to make sure user has entered valid dates...

 

[selenau837]

Hello!

Hope someone can help. My code behind my command button is not working. It is opening Report1 but not the criteria in the query.

I have a form (form1) with 2 unbound text boxes on, called "txtDatefrm" and "txtDateto" in which dates can be entered. There is also a command button on the form which searches for these dates and then opens a report(Report1). The report's control source is a query. (query1). On the ExpiryDate field in the query I have the criteria:
Between ([forms]![form1]![txtDateFrm]) and ([forms]![form1]![txtDateTo])

Behind the command button I have the code:
Dim stdocname as string
Dim ExpiryDate as Date
txtDateFrm.setfocus
ExpiryDate = txtDateFrm.text
If Trim(ExpiryDate) <>"" Then
stdocname = "Report1"
Docmd.Openreport stdocname,acViewPreview
End if

Many Thanks for any suggestions!

One thing I see wrong is you aren't having it open with a filter.

You need to creat a varibles that will hold that criteria for you, and then use it in the opening arugemnts for the Docmd.OpenReports.

Example
Dim strFilter...

then make strFilter what ever you want it to be. That is your where condition.

Then
DoCmd.OpenReport stDocName, acViewPreview, , strFilter

 

[Maz100]

The report and query runs perfectly it's when I use the search form and button it all goes wrong!

Thanks for reply

 

[Maz100]

The report and query runs perfectly it's when I use the search form and button it all goes wrong!

Thanks for reply