oxicottin
Learning by pecking away....
- Local time
- Yesterday, 22:56
- Joined
- Jun 26, 2007
- Messages
- 856
Hello, im having trouble with this search forms Where. Sometimes it gives me the results sometimes I get an error.. I want to call starttDate and give me results from that date and same with end date, I want end date to give me results to that date.
I call function using
Me.RecordSource = "SELECT * FROM qry_AdvancedSearch " & BuildFilter
Here is the code for the search form.
I call function using
Me.RecordSource = "SELECT * FROM qry_AdvancedSearch " & BuildFilter
Here is the code for the search form.
Code:
'*********************************FILTER SEARCH START*********************************
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
varWhere = Null ' Main filter
'*************************************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'*************************************************************************************
'Employee
If Not IsNull(Me.cboEmployees) Then
varWhere = varWhere & "[EmployeeID] = " & Me.cboEmployees & " AND "
End If
'Line
If Not IsNull(Me.cboLine) Then
varWhere = varWhere & "[MachineID] = " & Me.cboLine & " AND "
End If
'Lengh
If Not IsNull(Me.cboLength) Then
varWhere = varWhere & "[Length] = '" & Replace(Me.cboLength, "'", "''") & "' AND "
End If
'Check for LIKE in Keyword Search
Me.txtProductionContains.SetFocus
If Me.txtProductionContains > "" Then
varWhere = varWhere & "[ProductionProblems] LIKE ""*" & Me.txtProductionContains & "*"" AND "
End If
'Product
If Not IsNull(Me.cboProduct) Then
varWhere = varWhere & "[ProductID] = " & Me.cboProduct & " AND "
End If
'Start date
' If Not IsNull(Me.txtStartDate) Then
' varWhere = varWhere & "([ShiftDate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
' End If
'End Date
' If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
' varWhere = varWhere & "([ShiftDate] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
' End If
' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
' msg if no data
'MsgBox "No criteria", vbInformation, "Nothing to do."
Me.FilterOn = True
Else
varWhere = "WHERE " & varWhere
' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function
'
'*********************************FILTER SEARCH END***********************************