Filter Text Syntax

[JK]

I'm having a syntax issue trying to filter a text field on a subform with a
combo box on a main form. The lines in question have to do with combo box
cboShift. The field "Shift" is a text field on my subform.

Any help would be appreciated.

Private Sub cmdApplyFilter_Click()
Dim strWhere As String
Dim lngLen As Long

Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.cboFilterByItem) Then
strWhere = strWhere & "([ProductID] = " & Me.cboFilterByItem & ")
AND "
End If

If Not IsNull(Me.cboShift) Then
strWhere = strWhere & "([Shift] = " & Me.cboShift & ") AND "
End If

If (Me.optFilterSort = 1) Then
If Not IsNull(Me.txtDateBegin) Then
strWhere = strWhere & "([RequestDate] >= " & Format(Me.txtDateBegin,
conJetDate) & ") AND "
End If
If Not IsNull(Me.txtDateEnd) Then
strWhere = strWhere & "([RequestDate] < " & Format(Me.txtDateEnd +
1, conJetDate) & ") AND "
End If
Else

If (Me.optFilterSort = 2) Or (Me.optFilterSort = 3) Then
If Not IsNull(Me.txtDateBegin) Then
strWhere = strWhere & "([CompleteDate] >= " &
Format(Me.txtDateBegin, conJetDate) & ") AND "
End If
If Not IsNull(Me.txtDateEnd) Then
strWhere = strWhere & "([CompleteDate] < " & Format(Me.txtDateEnd +
1, conJetDate) & ") AND "
End If
End If
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Forms![frmLineScheduling]![frmWorksheetEntry_SF].Form.Filter =
strWhere
Forms![frmLineScheduling]![frmWorksheetEntry_SF].Form.FilterOn = True
End If

End Sub

 

[Dirk Goldgar]

I'm having a syntax issue trying to filter a text field on a subform with
a
combo box on a main form. The lines in question have to do with combo box
cboShift. The field "Shift" is a text field on my subform. [...]

If Not IsNull(Me.cboShift) Then
strWhere = strWhere & "([Shift] = " & Me.cboShift & ") AND "
End If

Are the quoted lines of code the ones that are giving you trouble? If Shift
is a text field, as you say, then you need to embed quotes (single or
double) around the value you get from the combo box. For example:

If Not IsNull(Me.cboShift) Then
strWhere = strWhere & "([Shift] = '" & Me.cboShift & "') AND "
End If

I used single quotes in the above, based on the assumption that the Shift
field will never contain the single-quote character ('). If it will, you
can use double-quotes instead, but it's trickier to get them into the
string.

 

[JK]

Perfect - thank you - I can never seem to get those right.

I'm having a syntax issue trying to filter a text field on a subform with
a
combo box on a main form. The lines in question have to do with combo box
cboShift. The field "Shift" is a text field on my subform. [...]

If Not IsNull(Me.cboShift) Then
strWhere = strWhere & "([Shift] = " & Me.cboShift & ") AND "
End If

Are the quoted lines of code the ones that are giving you trouble? If Shift
is a text field, as you say, then you need to embed quotes (single or
double) around the value you get from the combo box. For example:

If Not IsNull(Me.cboShift) Then
strWhere = strWhere & "([Shift] = '" & Me.cboShift & "') AND "
End If

I used single quotes in the above, based on the assumption that the Shift
field will never contain the single-quote character ('). If it will, you
can use double-quotes instead, but it's trickier to get them into the
string.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)