Filter subform in a form based on date range

[guestbb]

So I have a search form that I created that has also a subform (a continous form, that has a query as its source) where my records are listed. I started on making a Between dates filter where i would be able to pick dates and get the results of records that are in that range.

I put two text boxes in which I set format to Short Date so that I get the button to select the date from a calendar for each text box. Then I made a button that is only for this and added a Clickon code so that it filters my subform over the recordsource of the form. The problem is is that i get an error and it doesnt filter

If anyone knows how to fix this or has a better idea. please do tell

Code is:

Private Sub btnDatumUpis_Click()
Dim SQL As String
    
    SQL = "SELECT qrySearchV.VID, qrySearchV.MarkVoz, qrySearchV.ModelVoz, " _
        & "qrySearchV.TipMot, qrySearchV.Regist, qrySearchV.VlaVoz, " _
        & "qrySearchV.KorVoz, qrySearchV.KatV, qrySearchV.DatumUVoz, " _
        & "qrySearchV.BrojS, qrySearchV.VrsPog, qrySearchV.VrsGo " _
        & "FROM qrySearchV  " _
        & "WHERE DatumUVoz [B]BETWEEN #" & Me.txtOdDatum & "# AND #" & Me.txtDoDatum & "#[/B] " _        
        & "ORDER BY qrySearchV.MarkVoz"
        
    
    Me.subPretrVoz.Form.RecordSource = SQL
    Me.subPretrVoz.Form.Requery
End Sub

 

[bob fitz]

So what is the error?

 

[arnelgp]

& "WHERE DatumUVoz BETWEEN #" & FORMAT(Me.txtOdDatum,"MM\/DD\/YYYY") & "# AND #" & FORMAT(Me.txtDoDatum,"MM\/DD\/YYYY") & "# " _ & "ORDER BY qrySearchV.MarkVoz"

 

[AlexN]

I would try this :

Private Sub btnDatumUpis_Click()
Dim SQL As String
[COLOR="Red"]Dim myDate1 as Date
Dim myDate2 as Date

myDate1= Format(txtOddatum, "yyyy\/mm\/dd")
myDate2= Format(txtDodatum, "yyyy\/mm\/dd")[/COLOR]
    
    SQL = "SELECT qrySearchV.VID, qrySearchV.MarkVoz, qrySearchV.ModelVoz, " _
        & "qrySearchV.TipMot, qrySearchV.Regist, qrySearchV.VlaVoz, " _
        & "qrySearchV.KorVoz, qrySearchV.KatV, qrySearchV.DatumUVoz, " _
        & "qrySearchV.BrojS, qrySearchV.VrsPog, qrySearchV.VrsGo " _
        & "FROM qrySearchV  " _
        & "WHERE DatumUVoz BETWEEN #" & [COLOR="red"]myDate1[/COLOR] & "# AND #" & [COLOR="red"]myDate2[/COLOR] & "# " _        
        & "ORDER BY qrySearchV.MarkVoz"
        
    
    Me.subPretrVoz.Form.RecordSource = SQL
    Me.subPretrVoz.Form.Requery
End Sub

Just a guess




Edit: arnelgp got there first!

 

[guestbb]

thank you gentleman. you helped a lot with your service.

but do you have any solution for if one of the field is left empty? for example if only from is inserted that it still shows all of the records from that date or it only end date is inserted that it shows all record to that date?

EDIT:
I managed to do it my self. simple way.

If IsNull(Me.txtOdDatum) Then
    Me.txtOdDatum = "1.1.1900."
    End If
    
    If IsNull(Me.txtDoDatum) Then
    Me.txtDoDatum = Date
    End If

 

[arnelgp]

Private Sub btnDatumUpis_Click()
Dim SQL As String

SQL = "SELECT qrySearchV.VID, qrySearchV.MarkVoz, qrySearchV.ModelVoz, " _
& "qrySearchV.TipMot, qrySearchV.Regist, qrySearchV.VlaVoz, " _
& "qrySearchV.KorVoz, qrySearchV.KatV, qrySearchV.DatumUVoz, " _
& "qrySearchV.BrojS, qrySearchV.VrsPog, qrySearchV.VrsGo " _
& "FROM qrySearchV Where "

If IsNull(Me.txtOdDatum) And IsNull(Me.txtDoDatum) Then
' all date included
SQL = SQL & "1=1 "
ElseIf IsNull(Me.txtOdDatum) Then
'from beginning up to Me.txtDoDadum date
SQL = SQL & "DatumUVoz <= #" & Format(Me.txtDoDatum, "mm\/dd\/yyyy") & "# "

ElseIf IsNull(Me.txtDoDatum) Then
'from start of Me.txtDoDatum up to end
SQL = SQL & "DatumUVoz >= #" & Format(Me.txtOdDatum, "mm\/dd\/yyyy") & "# "

Else
'inclusive dates
SQL = SQL & "DatumUVoz BETWEEN #" & Me.txtOdDatum & "# AND #" & Me.txtDoDatum & "# "
End If

SQL = SQL & "ORDER BY qrySearchV.MarkVoz"




Me.subPretrVoz.Form.RecordSource = SQL
Me.subPretrVoz.Form.Requery
End Sub