Searching by secondary criteria when first results in multiple hits?

[teresamichele]

Hi all!



I'm creating a database for employee time records (which I've mentioned in other posts). I've gotten help here so far but now I have a question I'm sure there's an easy answer to - I just can't seem to connect it in my head!

I want people to be able to search by last name for records, therefore avoiding "no results" based on people spelling first names incorrectly, using nick names, etc.

However, we have a husband and wife who both work here, plus two non-related people who have the same surname.

What I would like to have happen is if someone searches "Doe" then the records for John Doe come up because he's the only Doe in the records. I know how to do that part. However, if someone searches "Smith" it would then ask for a first name, since there's two of those in the Employee table.

I know there's an IIf function in there somewhere, I just don't know how to phrase it! Can anyone help me?

[maximus]

This is a simple example that I have here. I have a table called table1 with three Fields ID{Autonumber PK}, FName, LName.

I have first determine the RecordCount using the RecordCount Function. The Code is exited when there is no Identical Record to the Last Name entered in the strLastName input Box. When there is only one identical value that is displayed when is more than 1 then the user is prompted to type the FirstName.

Note: in case I have two names Like smith abraham, smith jonathan. When u type smith in the last name input box u will be prompted to type the first name, the query will return blank value if u type mark for the first name as there is no value where FName='mark' and LName='smith'

In this code below I have used Recordcount to determine whether the last name is unique and depending it on it I have created my criteria which I have used to filter a form whose record source is Table1.

Dim strFirstName As String
Dim strLastName As String
Dim intRecordcount As Integer
Dim strCriteria As String

strLastName = InputBox("Please Type the Last Name")
intRecordcount = IIf(IsNull(DCount("[ID]", "Table1", "[LName]='" & strLastName & "'")), 0, DCount("[ID]", "Table1", "[LName]='" & strLastName & "'"))

If intRecordcount = 0 Then
MsgBox "No Last Name matching the one typed exist in the table"
Exit Sub
End If
If intRecordcount > 1 Then
strFirstName = InputBox("Please type First Name as multiple records are available with the same last Name")
strCriteria = "[FName]='" & strFirstName & "' And [LName]='" & strLastName & "'"
Else
strCriteria = "[LName]='" & strLastName & "'"
End If
***************************
Depending on where you want the code to run from fill in the asterix line.

From a command button on the form or on open event

Me.Filter = strCriteria
Me.FilterOn = True

Command button on another form:
DoCmd.OpenForm "Table1", , , strCriteria

here in this code i have used to create a query depending on the criteria and then opened a form whose record source is the query created to display the result.

1) Create a Query based on the Last Name when its unique.
2) Create a Query based on Last and First Name when Last Name has more that one identical values in table1

I am running this code from a command button in an unbound form Form2. I have another tabular form whose recordsource is Myquery and i open it using Docmd.openform to display the result.

Dim strSQL As String
Dim strFirstName As String
Dim strLastName As String
Dim intRecordcount As Integer
Dim strDropQuery As String
Dim sdf As Object


strDropQuery = "Drop Table MyQuery"

strLastName = InputBox("Please Type the Last Name")
intRecordcount = IIf(IsNull(DCount("[ID]", "Table1", "[LName]='" & strLastName & "'")), 0, DCount("[ID]", "Table1", "[LName]='" & strLastName & "'"))

If intRecordcount = 0 Then
MsgBox "No Last Name matching the one typed exist in the table"
Exit Sub
End If
If intRecordcount > 1 Then
strFirstName = InputBox("Please type First Name as multiple records are available with the same last Name")
On Error Resume Next
CurrentDb.Execute strDropQuery, dbfailonerror
strSQL = "Select * from Table1 where LName='" & strLastName & "' And FName='" & strFirstName & "'"
Set sdf = CurrentDb.CreateQueryDef("MyQuery", strSQL)
Else
On Error Resume Next
CurrentDb.Execute strDropQuery, dbfailonerror
strSQL = "Select * from Table1 where LName='" & strLastName & "'"
Set sdf = CurrentDb.CreateQueryDef("MyQuery", strSQL)
End If

DoCmd.OpenForm "Table11"