Search form with Unbound listbox (1 Viewer)

[Sharkiness]

Afternoon Guys,

I am a bit baffled at the moment. I am trying to create a search form that will provide the entries in my unbound textboxes as results in an unbound listbox.

The textboxes I have are:

txtCustomerForename - Text
txtCustomerSurname - Text
txtPostCode - Text
txtAccountNumber - NUmber
txtBusinessName - Text

The table to search is named tbldata

I would like the search to show me any combination of the above entered.

The problem I am having is that if I put in a customer forename and surname and company name, I had the search set to forname AND Surname or Postcode or AccountNumber or BusinessName

Is there any way I can run SQL to check what textboxes are complete then pull the results on all the information provided into the listbox.

Any help appreciated. If you need code I can try provide but I have messed around with so much now it doesnt work at all.

I'm banging my head against a wall.

 

[Simon_MT]

I've put a Search Form before the Clients or Contacts. Two fields for search fields and a Search [Type] are required then

Private Function ClientsCriteria() As String

    With CodeContextObject
        If .[Search] = "A" Then
            ClientsCriteria = "[Client Address1] like '" & "*" & .[Field2] & "*" & "' or [Client Address2] like '" & "*" & .[Field2] & "*" & "'"
        ElseIf .[Search] = "C" Then
            ClientsCriteria = "[Client Co Name] like '" & .[Field2] & "*" & "'"
        ElseIf .[Search] = "E" Then
            ClientsCriteria = "[Client Email] like '" & "*" & .[Field2] & "*" & "'"
        ElseIf .[Search] = "I" Then
            ClientsCriteria = "[Client] like '" & .[Field2] & "*" & "'"
        ElseIf .[Search] = "N" Then
            ClientsCriteria = "[Client Surname] like '" & .[Field1] & "*" & "' and [Client First Name] like '" & .[Field2] & "*" & "'"
        ElseIf .[Search] = "M" Then
            ClientsCriteria = "[Client Surname] like '" & .[Field1] & "*" & "' and [Client First Name] like '" & .[Field2] & "*" & "' and [Client Master]=True"
        ElseIf .[Search] = "P" Then
            ClientsCriteria = "[Client Postcode ] like '" & .[Field2] & "*" & "'"
        ElseIf .[Search] = "T" Then
            ClientsCriteria = "[Client Tele ] like '" & "*" & .[Field2] & "*" & "' or [Client Tele2 ] like '" & "*" & .[Field2] & "*" & "'"
        End If
    End With
End Function

Then

Function ClientsEntry() As String
    DoCmd.OpenForm "Clients Details", , , ClientsCriteria
End Function

Simon

 

[Sharkiness]

Not sure that will work for me as I want to be able to search on one textbox value which would be fine, or a combination of any.

So it could be

Surname And Business Name

Surname And Postcode

Postcode And Business name.

Not all fields have an entry though so if one of my fields are empty it tends to match on this and pulls out false positives.

Also i tried using OR but then if I put a Surname and Business Name the list box showed all results for Surname even if the the business name didnt match.

Is there any way that I can get it to do an And match only on fields with a value and exclude those with no value?

Cheers

 

[Simon_MT]

Then can create a Table for Searches and designate whether 1 or 2 Search Fields are required and if only one hide the second field. Then Desc1 and Desc2 to provide the Label Names for your Search Fields =[Search].[Column](1) and =[Search].[Column](2).

Then you can construct the ClientsCriteria and use wildcards you can select records with empty fields.

Simon

 

[John Big Booty]

Perhaps the sample here does the sort of thing you are after.

 

[Sharkiness]

Hi All,

It does seem to be working now with this code.

[FONT=Arial]Private Sub ListSearchresults()[/FONT]
 
[FONT=Arial] Dim strSQL As String[/FONT]
[FONT=Arial] Dim i As Integer[/FONT]
[FONT=Arial] Dim aSQL(8) As String[/FONT]
[FONT=Arial] Dim intCriteriaCount As Integer[/FONT]
 
[FONT=Arial] intCriteriaCount = 0[/FONT]
 
[FONT=Arial] If Not IsNull(Forms![frmList2]![txtCustomerName]) Then[/FONT]
[FONT=Arial]     aSQL(0) = "(([Forename] & IIf([MiddleName] Is Null,'',', ' & [MiddleName]) & ' ' & [tblDataset].[Surname]) Like '*" & Forms![frmList2]![txtCustomerName] & "*')"[/FONT]
 
[FONT=Arial] Else[/FONT]
[FONT=Arial]     aSQL(0) = ""[/FONT]
[FONT=Arial] End If[/FONT]
 
[FONT=Arial] If Not IsNull(Forms![frmList2]![txtREF1]) Then[/FONT]
[FONT=Arial]     aSQL(1) = "((tblDataset.REF1) Like '*" & Forms![frmList2]![txtREF1] & "*') "[/FONT]
[FONT=Arial] Else[/FONT]
[FONT=Arial]     aSQL(1) = ""[/FONT]
[FONT=Arial] End If[/FONT]
 
[FONT=Arial] If Not IsNull(Forms![frmList2]![txtREF2]) Then[/FONT]
[FONT=Arial]     aSQL(2) = " ((tblDataset.REF2) Like '*" & Forms![frmList2]![txtREF2] & "*') "[/FONT]
[FONT=Arial] Else[/FONT]
[FONT=Arial]     aSQL(2) = ""[/FONT]
[FONT=Arial] End If[/FONT]
 
[FONT=Arial] If Not IsNull(Forms![frmList2]![txtAddress]) Then[/FONT]
[FONT=Arial]     aSQL(3) = " (([address_line_1] & IIf([address_line_2] Is Null,'',', ' & [address_line_2]) & IIf([address_line_3] Is Null,'',', ' & [address_line_3]) & IIf([address_line_4] Is Null,'',', ' & [address_line_4]) & IIf([address_line_5] Is Null,'',', ' & [address_line_5])) Like '*" & Forms![frmList2]![txtAddress] & "*') "[/FONT]
[FONT=Arial] Else[/FONT]
[FONT=Arial]     aSQL(3) = ""[/FONT]
[FONT=Arial] End If[/FONT]
 
[FONT=Arial] If Not IsNull(Forms![frmList2]![txtPostCode]) Then[/FONT]
[FONT=Arial]     aSQL(4) = " ((tblDataset.post_code) Like '*" & Forms![frmList2]![txtPostCode] & "*')"[/FONT]
[FONT=Arial] Else[/FONT]
[FONT=Arial]     aSQL(4) = ""[/FONT]
[FONT=Arial] End If[/FONT]
 
[FONT=Arial] If Not IsNull(Forms![frmList2]![txtCode]) Then[/FONT]
[FONT=Arial]     aSQL(5) = " ((tblDataset.Code) Like '*" & Forms![frmList2]![txtCode] & "*') "[/FONT]
[FONT=Arial] Else[/FONT]
[FONT=Arial]     aSQL(5) = ""[/FONT]
[FONT=Arial] End If[/FONT]
 
[FONT=Arial] If Not IsNull(Forms![frmList2]![txtAccountNo]) Then[/FONT]
[FONT=Arial]     aSQL(6) = " ((tblDataset. AccountNo) Like '*" & Forms![frmList2]![ txtAccountNo] & "*') "[/FONT]
[FONT=Arial] Else[/FONT]
[FONT=Arial]     aSQL(6) = ""[/FONT]
[FONT=Arial] End If[/FONT]
 
[FONT=Arial] If Not IsNull(Forms![frmList2]![txtACode]) Then[/FONT]
[FONT=Arial]     aSQL(7) = " ((tblDataset.ACode) Like '*" & Forms![frmList2]![txtACode] & "*') "[/FONT]
[FONT=Arial] Else[/FONT]
[FONT=Arial]     aSQL(7) = ""[/FONT]
[FONT=Arial] End If[/FONT]
 
[FONT=Arial] If Not IsNull(Forms![frmList2]![txtAgent]) Then[/FONT]
[FONT=Arial]     aSQL(8) = " ((tblDataset.Agent) Like '*" & Forms![frmList2]![txtAgent] & "*') "[/FONT]
[FONT=Arial] Else[/FONT]
[FONT=Arial]     aSQL(8) = ""[/FONT]
[FONT=Arial] End If[/FONT]
 
 
[FONT=Arial] For i = 0 To 8[/FONT]
[FONT=Arial]     If aSQL(i) <> "" And intCriteriaCount > 0 Then[/FONT]
[FONT=Arial]         aSQL(i) = "AND " & aSQL(i)[/FONT]
[FONT=Arial]         intCriteriaCount = intCriteriaCount + 1[/FONT]
[FONT=Arial]     ElseIf aSQL(i) <> "" And intCriteriaCount = 0 Then[/FONT]
[FONT=Arial]         aSQL(i) = "WHERE (" & aSQL(i)[/FONT]
[FONT=Arial]         intCriteriaCount = 1[/FONT]
[FONT=Arial]     End If[/FONT]
[FONT=Arial] Next i[/FONT]
 
[FONT=Arial] If intCriteriaCount > 0 Then[/FONT]
[FONT=Arial]     aSQL(8) = ")"[/FONT]
[FONT=Arial] End If[/FONT]
 
[FONT=Arial] strSQL = "SELECT tblDataset.Grading, tblDataset.entry_date, tblDataset.Suspect_ID, [Forename] & IIf([MiddleName] Is Null,'',', ' & [MiddleName]) & ' ' & [Surname] AS Name, tblDataset.REF1, tblDataset.REF2, " & _[/FONT]
[FONT=Arial]         "[address_line_1] & IIf([address_line_2] Is Null,'',', ' & [address_line_2]) & IIf([address_line_3] Is Null,'',', ' & [address_line_3]) & IIf([address_line_4] Is Null,'',', ' & [address_line_4]) & IIf([address_line_5] Is Null,'',', ' & [address_line_5]) AS [Add], " & _[/FONT]
[FONT=Arial]         "tblDataset.post_code, tblDataset.C code, tblDataset.AccountNo, tblDataset.DOB, tblDataset.ACode, tblDataset.Agent, tblDataset.status " & _[/FONT]
[FONT=Arial]         "FROM tblDataset " & _[/FONT]
[FONT=Arial]         aSQL(0) & aSQL(1) & aSQL(2) & aSQL(3) & aSQL(4) & aSQL(5) & aSQL(6) & aSQL(7) & aSQL(8) & ";"[/FONT]
 
[FONT=Arial] lstResults.RowSource = strSQL[/FONT]
 
[FONT=Arial] txtRecordsCount = lstResults.ListCount[/FONT]

I call the Search List result on the buttonclick function of my search button.