Help with report dates

[mikelev]

I have a small form that has two date fields and an image that acts looks like a button:

txtStartDate
txtEndDate
Image20

The dates are used to limit the results of the report MR_CheckRegister. The following code is not working and returns all rows?

CLIENTTRANS.DATEENTERED is DATE/TIME: (3/8/2005 7:46:53 AM)

Private Sub Image20_Click()
Me.txtStartDate.SetFocus
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
   
   strSQL = "SELECT  DATEENTERED " & _
"FROM CLIENTTRANS"
strWhere = "WHERE"
strOrder = "ORDER BY CLIENTTRANS.DATEENTERED"
' set where clause conditions
If Not IsNull(Me.txtStartDate) Then
  strWhere = strWhere & " (CLIENTTRANS.DATEENTERED) >=  #" & Me.txtStartDate & "00:00:00#  and"
End If
If Not IsNull(Me.txtEndDate) Then
  strWhere = strWhere & " (CLIENTTRANS.DATEENTERED) <=  #" & Me.txtEndDate & "23:59:59#  and"
End If
strWhere = Mid(strWhere, 1, Len(strWhere) - 5) ' remove ' and'
Set qryDef = dbNm.QueryDefs("qry_MR_CR")
   qryDef.sql = strSQL & " " & strWhere & "" & strOrder
   DoCmd.close
      On Error Resume Next
   DoCmd.OpenReport "MR_CheckRegister", acViewPreview
End Sub

Any help sure would be appreciated, as I am out of ideas.


Cheers,

 

[Flippertje]

Ever tried another approach?

in the open event in your report:

me.recordsource ="SELECTstatement including the dates in the form"

in the click event of the button:

DoCmd.OpenReport "MR_CheckRegister", acViewPreview

Have fun!

Flipper

 

[traingamer]

Try using IsDate rather than Not IsNull - your fields may NOT be null.

If [COLOR=red]IsDate[/color](Me.txtStartDate) Then

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

& "[highlight] [/highlight]00:00:00#

Same for " 23:59:59#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244