Searching 4 fields (using 1 or many or all)

[icsnetuk]

Hi,

I have a table called applicants

There is a form also called applicants which I use to view / edit the data.

On my main dashboard form which is where all of the main options are for the database (also the startup form) I have 4 search boxes which are called:

First Name
Last Name
Company Name
Post Code

I also used to have two additional search fields which were Applicant Ref and Telephone Number. I had to break these away from the main search as a secondary search feature on the form as I couldnt get the VB code that I had to get over the fact that the last 2 fields are Integer, even though the phone number is text :/

So here is my VB for all 6 search boxes, remember I have removed Applicant_ref and phone_number from the senerio, but when I just delete the lines related to the 2 Ive removed I get errors.

VB:

Private Sub Search_Go_Click ()

Dim strCriteria As String

If Not IsNull(Me.lastn ame) Then
strCriteria = "[Last_name] = '" & Me.lastname & "' AND "
End If

If Not IsNull(Me.PostC ode) Then
strCriteria = strCriteria & "[Post_code] = '" & Me.PostCode & "' AND "
End If

If Not IsNull(Me.Phone Number) Then
strCriteria = strCriteria & "[Telephone_numbe r] = '" & Me.PhoneNumber & "' AND "
End If

If Not IsNull(Me.first name) Then
strCriteria = strCriteria & "[First_name] = '" & Me.firstname & "' AND "
End If

If Not IsNull(Me.Compa ny) Then
strCriteria = strCriteria & "[Company] = '" & Me.Company & "' AND "
End If

If Not IsNull(Me.appli cantRef) Then
strCriteria = strCriteria & "[Applicant_ref] = '" & Me.applicantRef & "' AND "
End If

strCriteria = Left(strCriteri a, Len(strCriteria ) - 5)

DoCmd.Minimize

DoCmd.OpenForm "Applicants ", acNormal, , strCriteria
End Sub

*************** *************** ************
If I just remove the lines related to applicantRef or phone number then it works the first time I run it, then when attempting to search again, I get no results.

Thank you in advance.

[PianoMan64]

Hi,

I have a table called applicants

There is a form also called applicants which I use to view / edit the data.

On my main dashboard form which is where all of the main options are for the database (also the startup form) I have 4 search boxes which are called:

First Name
Last Name
Company Name
Post Code

I also used to have two additional search fields which were Applicant Ref and Telephone Number. I had to break these away from the main search as a secondary search feature on the form as I couldnt get the VB code that I had to get over the fact that the last 2 fields are Integer, even though the phone number is text :/

So here is my VB for all 6 search boxes, remember I have removed Applicant_ref and phone_number from the senerio, but when I just delete the lines related to the 2 Ive removed I get errors.

VB:

Private Sub Search_Go_Click ()

Dim strCriteria As String

If Not IsNull(Me.lastn ame) Then
strCriteria = "[Last_name] = '" & Me.lastname & "' AND "
End If

If Not IsNull(Me.PostC ode) Then
strCriteria = strCriteria & "[Post_code] = '" & Me.PostCode & "' AND "
End If

If Not IsNull(Me.Phone Number) Then
strCriteria = strCriteria & "[Telephone_numbe r] = '" & Me.PhoneNumber & "' AND "
End If

If Not IsNull(Me.first name) Then
strCriteria = strCriteria & "[First_name] = '" & Me.firstname & "' AND "
End If

If Not IsNull(Me.Compa ny) Then
strCriteria = strCriteria & "[Company] = '" & Me.Company & "' AND "
End If

If Not IsNull(Me.appli cantRef) Then
strCriteria = strCriteria & "[Applicant_ref] = '" & Me.applicantRef & "' AND "
End If

strCriteria = Left(strCriteri a, Len(strCriteria ) - 5)

DoCmd.Minimize

DoCmd.OpenForm "Applicants ", acNormal, , strCriteria
End Sub

*************** *************** ************
If I just remove the lines related to applicantRef or phone number then it works the first time I run it, then when attempting to search again, I get no results.

Thank you in advance.

Hey icsnetuk,

The reason that you're having this problem is, you're opening a open with a filter applied. If you run that process again based on that filtered form, you are filtering your filtered data. Of course your result is going to blank. There is no way for any field to have two values at the same time.

What you're going to need to do is simply close the form, and open a new one with the new filter that you want.

Hope that helps,

Joe P.