Hi, I have created a piece of code that filters a sub form of diary records using criteria the user has selected or entered. It was working fine when I made it a month or so ago and now is seems to be returning incorrectly filtered records, for example, I enter 2 dates to return all diary entries between the 2 dates. Yet it misses some records out that should be within the date range, and it sometimes include records that are outside the date range. I am also getting an error when I try and filter the diary entries via the supplier, "data type mismatch", here's the code that, bearing in mind, was previously working fine.
Code:
Public Function filter_diary()
Dim dbs As Database
'Dim qdf As QueryDef
Dim Sqlstr As String
Dim sqlstrwhat As String
Set dbs = CurrentDb
Sqlstr = "SELECT tbl_PropertyDiary.* FROM tbl_PropertyDiary" 'INNER JOIN tbl_Reporting_Hierarchy ON tbl_PropertyDiary.BRANCHID = tbl_Reporting_Hierarchy.CostCentre"
If Nz(Me!Branch_Choice, "") = "" Then
Else
sqlstrwhat = "((tbl_PropertyDiary.BranchID)='" & Me!Branch_Choice & "') AND "
End If
If Nz(Me!Job_Choice, "") = "" Then
Else
sqlstrwhat = sqlstrwhat & "((tbl_PropertyDiary.JobType)='" & Me!Job_Choice & "') AND "
End If
If Nz(Me!Supplier_Choice, "") = "" Then
Else
sqlstrwhat = sqlstrwhat & "((tbl_PropertyDiary.Supplier)= '" & Me!Supplier_Choice & "') AND "
End If
If IsDate(Me!Sdate) And IsDate(Me!Edate) Then
sqlstrwhat = sqlstrwhat & "(tbl_PropertyDiary.Date_and_time) BETWEEN #" & Format(Me!Sdate, "dd/mmm/yyyy") & "# AND #" & Format(Me!Edate, "dd/mmm/yyyy") & "# AND "
Else
End If
If sqlstrwhat = "" Then
Sqlstr = Sqlstr
Else
Sqlstr = Sqlstr & " WHERE (" & Left(sqlstrwhat, (Len(sqlstrwhat) - 4)) & ")"
End If
'Set qdf = dbs.QueryDefs("qry_underlying_capex")
'qdf.SQL = sqlstr
'qdf.Close
Me.frm_diarySub.Form.RecordSource = Sqlstr
End Function
Any ideas would be greatly appreciated
Many thanks in advance
Regards
