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.
NDAlog-2013-09-20-1.accdb
CindyApplication Systems Analyst IIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ButlerTechnologyCommented:
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 & "'"      
   Else
       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.

Tom
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.