Require a field in search form

[Cathleen]

I have a search form with the following code in the search button's OnClick
event. It works great as long as the user selects one or more items in the
multi-select list box [lstStationID]. If nothing is selected, the where
string has unneccsary characters on the end of it. So, I think I could do one
of two things: 1) If nothing is selected, have code that chops off more
characters at the end of the where string or 2) require the user to select
one or more items and have a message box appear if the user clicks the search
button without selecting anything. However, I am having trouble getting
either of these methods to work. Does anyone have any suggestions on how to
handle this?
Thank you!

Private Sub cmdInSituFilter_Click()
Dim StrWhere As String
Dim lngLen As Long
Dim VarItem As Variant
Dim i As Integer
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.lstWaterbody) Then
StrWhere = StrWhere & "[waterbody_id] = " & Me.lstWaterbody & " AND "
End If

If Not IsNull(Me.txtStartDate) Then
StrWhere = StrWhere & "[sampling_date] >= " &
Format(Me.txtStartDate, conJetDate) & " AND "
End If

If Not IsNull(Me.txtEndDate) Then
StrWhere = StrWhere & "[sampling_date] <= " & Format(Me.txtEndDate,
conJetDate) & " AND "
End If

If Me.chkSurface = True Then
StrWhere = StrWhere & "[depth_round] = " & 0 & " AND "
End If

StrWhere = StrWhere & "([station_id] IN("
For i = 0 To Me.lstStationID.ListCount - 1
If Me.lstStationID.Selected(i) Then
StrWhere = StrWhere & "'" & Me.lstStationID.Column(0, i) & "',"
End If
Next i


StrWhere = Left(StrWhere, Len(StrWhere) - 2) & "'))"

Debug.Print StrWhere

Me.Filter = StrWhere
Me.FilterOn = True

End Sub

 

[Cathleen]

Thank you! I had to modify it to work with the characters that were
appearing, but your suggestion of adding this worked:
If Right(StrWhere,5) = " AND " Then

StrWhere = Left(StrWhere,Len(StrWhere) - 5)
End If

All seems to be working perfectly now.
Thanks again!

What result are you getting from Debug.Print? Does the code compile?

One thing I see is that there seem to be some misplaced quotes in this line:
StrWhere = StrWhere & " ' " & Me.lstStationID.Column(0, i) & " ',"

You have the ampersand as part of the literal string, and not quite the right
order and number of single and double quotes. Try something like:
StrWhere = StrWhere & ' " & Me.lstStationID.Column(0, i) & " ' "

The comma could be in there, I guess, but it looks as if all you do is remove
it. Actually, your remove two characters for reasons I cannot see.

You could test StrWhere at the end of the code:

If Right(StrWhere,5) = " AND " Then
StrWhere = Left(StrWhere,Len(StrWhere) - 5)
End If

If you need to remove a character or two if there is a list box selection:

Dim lngLeft as Long

If Right(StrWhere,5) = " AND " Then
lngLeft = 5
Else
lngLeft = 2
End If

If lngLeft = 2 Then
StrWhere = StrWhere & "'))"
End If

Alternatively, you could test the list box for a value, and skip adding to
the string if there is no selection. However, I think something like I have
suggested will involve less coding.

I have a search form with the following code in the search button's OnClick
event. It works great as long as the user selects one or more items in the
multi-select list box [lstStationID]. If nothing is selected, the where
string has unneccsary characters on the end of it. So, I think I could do one
of two things: 1) If nothing is selected, have code that chops off more
characters at the end of the where string or 2) require the user to select
one or more items and have a message box appear if the user clicks the search
button without selecting anything. However, I am having trouble getting
either of these methods to work. Does anyone have any suggestions on how to
handle this?
Thank you!

Private Sub cmdInSituFilter_Click()
Dim StrWhere As String
Dim lngLen As Long
Dim VarItem As Variant
Dim i As Integer
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.lstWaterbody) Then
StrWhere = StrWhere & "[waterbody_id] = " & Me.lstWaterbody & " AND "
End If

If Not IsNull(Me.txtStartDate) Then
StrWhere = StrWhere & "[sampling_date] >= " &
Format(Me.txtStartDate, conJetDate) & " AND "
End If

If Not IsNull(Me.txtEndDate) Then
StrWhere = StrWhere & "[sampling_date] <= " & Format(Me.txtEndDate,
conJetDate) & " AND "
End If

If Me.chkSurface = True Then
StrWhere = StrWhere & "[depth_round] = " & 0 & " AND "
End If

StrWhere = StrWhere & "([station_id] IN("
For i = 0 To Me.lstStationID.ListCount - 1
If Me.lstStationID.Selected(i) Then
StrWhere = StrWhere & "'" & Me.lstStationID.Column(0, i) & "',"
End If
Next i


StrWhere = Left(StrWhere, Len(StrWhere) - 2) & "'))"

Debug.Print StrWhere

Me.Filter = StrWhere
Me.FilterOn = True

End Sub