By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

Searching 4 fields (using 1 or many or all)

P: 1
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.lastname) Then
strCriteria = "[Last_name] = '" & Me.lastname & "' AND "
End If

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

If Not IsNull(Me.PhoneNumber) Then
strCriteria = strCriteria & "[Telephone_number] = '" & Me.PhoneNumber & "' AND "
End If

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

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

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

strCriteria = Left(strCriteria, 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.
Sep 27 '08 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 374
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.lastname) Then
strCriteria = "[Last_name] = '" & Me.lastname & "' AND "
End If

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

If Not IsNull(Me.PhoneNumber) Then
strCriteria = strCriteria & "[Telephone_number] = '" & Me.PhoneNumber & "' AND "
End If

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

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

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

strCriteria = Left(strCriteria, 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.
Sep 27 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.