Format Data as Date/Time

[mikelev]

I may be going about this all wrong? Each record has a date/time stamp, this value is defaulted on the form.

I have a small report search form that users enter a start date (txtStartDate) and end date (txtEndDate) with an image that has the following onclick event.

Private Sub Image20_Click()
    Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
   
strSQL = "SELECT  * " & _
"FROM CAGEINVCOUNT"
strWhere = "WHERE"
strOrder = "ORDER BY CAGEINVCOUNT.id"
' set where clause conditions
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " (CAGEINVCOUNT.Insertdate) >= format(Me.txtStartDate,'general date')  and"
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " (CAGEINVCOUNT.insertdate) < format(Me.txtEndDate,'general date')  and"
End If
strWhere = strWhere & " (CAGEINVCOUNT.code) = 'C'   and"
strWhere = Mid(strWhere, 1, Len(strWhere) - 5) ' remove ' and'
Set qryDef = dbNm.QueryDefs("qrycageinvreport")
   qryDef.sql = strSQL & " " & strWhere & "" & strOrder
   'DoCmd.close
   DoCmd.OpenReport "rptcageinvSA", acViewPreview
End Sub

This is what I am hoping for:

User enters start date: 1/27/2005 and form will change that value to a general date (1/27/2005 00:00:01 AM)

User enters end date: 1/27/2005 and form will change that value to a general date (1/27/2005 11:59:59 PM)

Any suggestions would be appreciated.

 

[PHV]

Something like this ?
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " (CAGEINVCOUNT.Insertdate) >= #" & Me.txtStartDate & " 00:00:00# and"
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " (CAGEINVCOUNT.insertdate) <= #" & Me.txtEndDate & " 23:59:59# and"
End If

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

 

[mikelev]

Perfect!

Many thanks for the help PHV, a star for ya!