Filter diary data using two dates in vba (1 Viewer)

[brainox]

I have a sub form dragged into a main form that shows all existing diary entries. I have already got code for various different filters but I am struggling to wrap my head around this one. Just want the user to input two dates and then the sub form to show all the diary entries between those 2 dates. Or maybe a drop down box listing various lengths of time from today's date and showing entries within that time scale. Whatever is easiest tbh. Would I have to use date diff for this? Many thanks in advance

 

[pr2-eugin]

Add this as another Criteria to the WHERE clause of the SubForm's Recordsource..

 

[brainox]

Add this as another Criteria to the WHERE clause of the SubForm's Recordsource..

Add what sorry?
Cheers

 

[pr2-eugin]

Add what sorry?
Cheers

So how are you creating this Filter? Is it just DoCmd.ApplyFilter? Or you generating the SQL on the fly?

 

[brainox]

So how are you creating this Filter? Is it just DoCmd.ApplyFilter? Or you generating the SQL on the fly?

There is a subform linked to a table where diary entries are saved and the subform retrieves them, in the header of the form there are various combo boxes where the user selects the criteria and then clicks filter and it filters the data.

Here's the code for the filter button for one of the criteria;

Dim dbs As Database
Dim qdf As QueryDef
Dim sqlstr As String
Dim sqlstrwhat As String

Set dbs As CurrentDb

sqlstr = "SELECT tbl_propertyDiary.* FROM tbl;_propertyDiary"

If Nz(Me!Branch_Choice, "") = "" Then
Else sqlstrwhat = "((tbl_propertyDiary.BranchID)='" & Me!Branch_Choice & "') AND "
End If

'Other criteria

If sqlstrwhat = "" Then
sqlstr = sqlstr
Else
sqlstr = sqlstr & " WHERE (" & Left(sqlstrwhat, (Len(sqlstrwhat) - 4)) & ")"
End If

Me.frm_diarySub.Form.RecordSource = sqlstr

Cheers

 

[pr2-eugin]

This is what I meant..

Dim dbs As Database
Dim qdf As QueryDef
Dim sqlstr As String
Dim sqlstrwhat As String

Set dbs As CurrentDb

sqlstr = "SELECT tbl_propertyDiary.* FROM tbl_propertyDiary"

[COLOR=Green]'Other criteria[/COLOR]

[B]If IsDate(Me![COLOR=Blue]yourStartDateBox[/COLOR]) And IsDate(Me![COLOR=Blue]yourEndDateBox[/COLOR]) Then
    sqlstrwhat = "((tbl_propertyDiary.[COLOR=Blue]DateFieldName[/COLOR]) BETWEEN #" & Me![COLOR=Blue]yourStartDateBox[/COLOR] & "# AND #" & Me![COLOR=Blue]yourEndDateBox[/COLOR] & "#) AND "
End If[/B]

[COLOR=Green]'Other criteria

[/COLOR]If sqlstrwhat = "" Then
    sqlstr = sqlstr
Else
    sqlstr = sqlstr & " WHERE (" & Left(sqlstrwhat, (Len(sqlstrwhat) - 4)) & ")"
End If

Me.frm_diarySub.Form.RecordSource = sqlstr

 

[brainox]

This is what I meant..

Dim dbs As Database
Dim qdf As QueryDef
Dim sqlstr As String
Dim sqlstrwhat As String

Set dbs As CurrentDb

sqlstr = "SELECT tbl_propertyDiary.* FROM tbl_propertyDiary"

[COLOR=Green]'Other criteria[/COLOR]

[B]If IsDate(Me![COLOR=Blue]yourStartDateBox[/COLOR]) And IsDate(Me![COLOR=Blue]yourEndDateBox[/COLOR]) Then
    sqlstrwhat = "((tbl_propertyDiary.[COLOR=Blue]DateFieldName[/COLOR]) BETWEEN #" & Me![COLOR=Blue]yourStartDateBox[/COLOR] & "# AND #" & Me![COLOR=Blue]yourEndDateBox[/COLOR] & "#) AND "
End If[/B]

[COLOR=Green]'Other criteria

[/COLOR]If sqlstrwhat = "" Then
    sqlstr = sqlstr
Else
    sqlstr = sqlstr & " WHERE (" & Left(sqlstrwhat, (Len(sqlstrwhat) - 4)) & ")"
End If

Me.frm_diarySub.Form.RecordSource = sqlstr

Cheers, this has certainly helped a lot
But if I use long date formatting (which is what the field in the table and sub form are set to) only one of the two dates in the header show a date and the the other comes up with "#######" when the other is in focus

Here's the code you gave me but with a little bit of editing

If Nz(IsDate(Me!Sdate, "")) And Nz(IsDate(Me!Edate, "")) Then
Else
sqlstrwhat = sqlstrwhat & "((tbl_propertyDiary.Date_and_time) BETWEEN #" & Me!Sdate & "# AND #" & Me!Edate & "#) AND "
End If

 

[pr2-eugin]

You need to fix the width of the controls as long as possible to accommodate the maximum length of the Longest date it could take in.. That would sort your trouble..

Regarding using Nz() in the If statement is not required.. As IsDate will evaluate Null and Empty String values..

Dim dbs As Database
Dim qdf As QueryDef
Dim sqlstr As String
Dim sqlstrwhat As String

Set dbs As CurrentDb

sqlstr = "SELECT tbl_propertyDiary.* FROM tbl_propertyDiary"

[COLOR=Green]'Other criteria[/COLOR]

[B]If IsDate(Me![COLOR=Blue]yourStartDateBox[/COLOR]) And IsDate(Me![COLOR=Blue]yourEndDateBox[/COLOR]) Then
    sqlstrwhat = [/B][B]sqlstrwhat & [/B][B]"((tbl_propertyDiary.[COLOR=Blue]DateFieldName[/COLOR]) BETWEEN #" & Me![COLOR=Blue]yourStartDateBox[/COLOR] & "# AND #" & Me![COLOR=Blue]yourEndDateBox[/COLOR] & "#) AND "
End If[/B]

[COLOR=Green]'Other criteria

[/COLOR]If sqlstrwhat = "" Then
    sqlstr = sqlstr
Else
    sqlstr = sqlstr & " WHERE (" & Left(sqlstrwhat, (Len(sqlstrwhat) - 4)) & ")"
End If

Me.frm_diarySub.Form.RecordSource = sqlstr