List Box Search

[Mathew Winder]

I'm creating a form that has a list box that will be used to select students
from a database. I'm working to create a small search so that users can
enter a student's first and last name and then hit a "Search" button that
changes the record source on the list to show only the records that match the
name.

I've gotten it to work when both text fields are filled in, but I'm having
trouble with the if statement that tells it to search for only the first or
only the last name if the other box is not filled in.

The code looks something like this:

Private Sub comSearch_Click()
If Me!txtFN = Empty And Me!txtLN <> Empty Then
Me!StudentList.RowSource = "SELECT [Fields] FROM Students WHERE
Students.[Last Name] = '" & stuLN & "';"
Else
If Me!txtFN <> Empty And Me!txtLN = Empty Then
Me!StudentList.RowSource = "SELECT [Fields] FROM Students WHERE
Students.[First Name] = '" & stuFN & "';"
Else
If Me!txtFN <> Empty And Me!txtLN <> Empty Then
Me!StudentList.RowSource = "SELECT [Fields] FROM Students
WHERE Students.[First Name] = '" & stuFN & "' AND Students.[Last Name] = '" &
stuLN & "';"
End If
End If
End If
End Sub

Where txtFN and txtLN are the two text boxes on the form, comSearch is the
Search command button, and stuFN and stuLN are the two variables that are
updated with the text from the text boxes. The code doesn't run at all if
only one box or the other is filled in - so I assume I'm just missing
something with the "Me!txtFN = Empty" bit.

Any help would be much appreciated.

 

[Dale Fye]

Actually, the easiest way to do this is:


Private Sub comSearch_Click()

Dim strSQL as string

strSQL = "SELECT [Field1], [Field2], [Field3] " _
& "FROM [Students] " _
& "WHERE [Last Name] Like '*" & me.txtLN & "*'" _
& " AND [First Name] Like '*" & me.txtFN & "*'"

me.StudentList.RowSource = strSQL

End Sub

By using the Like criteria, it allows you to search for similar strings.
If you only want to search for those where the [Frist Name] and [Last Name]
start with the characters that have been entered, then drop the leading '*
from each of those lines.

 

[Mathew Winder]

A much quicker, easier, and more effective fix.

Thanks a lot for the tip!

Actually, the easiest way to do this is:


Private Sub comSearch_Click()

Dim strSQL as string

strSQL = "SELECT [Field1], [Field2], [Field3] " _
& "FROM [Students] " _
& "WHERE [Last Name] Like '*" & me.txtLN & "*'" _
& " AND [First Name] Like '*" & me.txtFN & "*'"

me.StudentList.RowSource = strSQL

End Sub

By using the Like criteria, it allows you to search for similar strings.
If you only want to search for those where the [Frist Name] and [Last Name]
start with the characters that have been entered, then drop the leading '*
from each of those lines.

----
HTH
Dale

I'm creating a form that has a list box that will be used to select students
from a database. I'm working to create a small search so that users can
enter a student's first and last name and then hit a "Search" button that
changes the record source on the list to show only the records that match the
name.

I've gotten it to work when both text fields are filled in, but I'm having
trouble with the if statement that tells it to search for only the first or
only the last name if the other box is not filled in.

The code looks something like this:

Private Sub comSearch_Click()
If Me!txtFN = Empty And Me!txtLN <> Empty Then
Me!StudentList.RowSource = "SELECT [Fields] FROM Students WHERE
Students.[Last Name] = '" & stuLN & "';"
Else
If Me!txtFN <> Empty And Me!txtLN = Empty Then
Me!StudentList.RowSource = "SELECT [Fields] FROM Students WHERE
Students.[First Name] = '" & stuFN & "';"
Else
If Me!txtFN <> Empty And Me!txtLN <> Empty Then
Me!StudentList.RowSource = "SELECT [Fields] FROM Students
WHERE Students.[First Name] = '" & stuFN & "' AND Students.[Last Name] = '" &
stuLN & "';"
End If
End If
End If
End Sub

Where txtFN and txtLN are the two text boxes on the form, comSearch is the
Search command button, and stuFN and stuLN are the two variables that are
updated with the text from the text boxes. The code doesn't run at all if
only one box or the other is filled in - so I assume I'm just missing
something with the "Me!txtFN = Empty" bit.

Any help would be much appreciated.

 

[Dale Fye]

Opps.

if you only want the ones where the names start with the characters, drop
the *, not the single quote.

----
HTH
Dale

A much quicker, easier, and more effective fix.

Thanks a lot for the tip!

Actually, the easiest way to do this is:


Private Sub comSearch_Click()

Dim strSQL as string

strSQL = "SELECT [Field1], [Field2], [Field3] " _
& "FROM [Students] " _
& "WHERE [Last Name] Like '*" & me.txtLN & "*'" _
& " AND [First Name] Like '*" & me.txtFN & "*'"

me.StudentList.RowSource = strSQL

End Sub

By using the Like criteria, it allows you to search for similar strings.
If you only want to search for those where the [Frist Name] and [Last Name]
start with the characters that have been entered, then drop the leading '*
from each of those lines.

----
HTH
Dale

I'm creating a form that has a list box that will be used to select students
from a database. I'm working to create a small search so that users can
enter a student's first and last name and then hit a "Search" button that
changes the record source on the list to show only the records that match the
name.

I've gotten it to work when both text fields are filled in, but I'm having
trouble with the if statement that tells it to search for only the first or
only the last name if the other box is not filled in.

The code looks something like this:

Private Sub comSearch_Click()
If Me!txtFN = Empty And Me!txtLN <> Empty Then
Me!StudentList.RowSource = "SELECT [Fields] FROM Students WHERE
Students.[Last Name] = '" & stuLN & "';"
Else
If Me!txtFN <> Empty And Me!txtLN = Empty Then
Me!StudentList.RowSource = "SELECT [Fields] FROM Students WHERE
Students.[First Name] = '" & stuFN & "';"
Else
If Me!txtFN <> Empty And Me!txtLN <> Empty Then
Me!StudentList.RowSource = "SELECT [Fields] FROM Students
WHERE Students.[First Name] = '" & stuFN & "' AND Students.[Last Name] = '" &
stuLN & "';"
End If
End If
End If
End Sub

Where txtFN and txtLN are the two text boxes on the form, comSearch is the
Search command button, and stuFN and stuLN are the two variables that are
updated with the text from the text boxes. The code doesn't run at all if
only one box or the other is filled in - so I assume I'm just missing
something with the "Me!txtFN = Empty" bit.

Any help would be much appreciated.