• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

Microsoft Access forms

In the attached database, I've created a form called fromsearchsample.  This is a forms that allows the user to enter the first name or  last name of an individual in the list, click search and filter out the user.  The form works fine, if I enter just the first name of someone on the list, the form will filter a list of individuals in which choose.  If I double click on the indiividual;s name a form of the individual's information will display.  The problem is if I enter the just the last name of an individual in the last name field, it will not  work correctly.  It does not display any names with the same last name.  Could you please provide the correct code in the form that will allow me to enter a first name only or last name only to perform a search of an individual?  Not first and last name in the field to get results.
1 Solution
The issue is with the logic for creating the where clause.  The current logic  will never use the else part of the last name code as the SQL string will never be empty as it points to the entire SQL statement.

I have added a new variable called sWHERE and I use that to developed the where clause and then concatenate with the SQL variable.

Dim strSQL As String
Dim sWhere As String
strSQL = "SELECT tbl_NDALOG.ID, [Last_Name] & " & Chr(34) & ", " & Chr(34) & " & [First_Name] AS FullName, " & _
"tbl_NDALOG.Last_Name, tbl_NDALOG.First_Name, tbl_NDALOG.MI FROM tbl_NDALOG "
If IsNull(Me.txtSearchFirstName) = False Then
    sWhere = sWhere & " WHERE First_Name='" & Me.txtSearchFirstName & "'"
End If
If IsNull(Me.txtSearchLastName) = False Then
   If sWhere = "" Then
       sWhere = sWhere & " WHERE Last_Name='" & Me.txtSearchLastName & "'"      
       sWhere = sWhere & " AND Last_Name='" & Me.txtSearchLastName & "'"
   End If
End If

Me.lstSearchResults.RowSource = strSQL & sWhere

Open in new window

I wasn't able to highlight my changes -- but you should be able to copy/paste the code snippet.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now