Search form will not return exact with wildcard SQL

[DevTycoon]

I built a search form to experiment with the conversation on the following post mentioned at the end of my post, ( if you want to view the link just navigate through the instructions. As of now I can not post links). I too am trying to create a search form with dynamic values but seem to run into a problem. Apologies to Eljefegeneo for hijacking his post.

I can not post the database. I did export the table data into a tabbed delimited format and will include that at the end. If you want to reproduce my bug copy that data in a txt file and import that into the table tblMain. Make a search form and a sub form. the sub form is linked to the table and the main form is unbound with two search buttons.

Problem Statement:
The code works fine. I did find a bug that seems to arise with the wild cards when the entire field values are entered. You can replicate the bug by testing the search criteria listed below. What are your thoughts? Thanks

This is a brief example of the bug. A detailed description is near the code below.

If my name is "Devtycoon" and I search "Dev" the SQL statement will build "*Dev*" and it will pull up my name, "Devtycoon". On the contrary if I search "Tycoon" the SQL statement will build "*tycoon*" and it will pull up my name, "Devtycoon". If i search "DevTycoon" the sql statement will build "*Devtycoon*" and no results will be returned. That is buggy because the name is in the database but no wildcards would be needed.

Table structure:

tblMain

• ClientID
• Surname [text]
• Organization [text]
• ProgramTitle [text]
• City [text]
• State[text]
• Zip4 [text]
• Telephone [text]

Form1 contains the controls for search criteria. Three text box controls are used to filter a sub form control called "DS". The sub form is called sFrmMain and is a datasheet that shows results of the search. there is a button that runs the code and another button that clears search criteria and shows all records. Both button's code set the sub form's record source using an SQL string built using a function that returns a segment of the overall search string using the contents of each control that then is concatenated into a SQL statement used to set the record source.

There is a function for the following components of the SQL statement

• SELECT / FROM
• WHERE
• controlA = me.txtSurname
• controlB = me.txtOrganization
• controlC = me.txtProgramTitle

This is how you can replicate the bug.

I tested two additional surnames organizations and program titles as follows:

Try example (1). you will get both the 4's and the 14's records returned.

If you type in letter for letter of the second record (the one with the 14's) no records populate. It is like the wild card does not like it if you type in the entire field value. Uncle Gizmo's and Allen Brown's method do the same thing were no records populate if the 14's entire surname organization and program title are entered into the text controls. Can you reproduce this error? Other than that I think either method is bulletproof.

Example criteria

1) put the following criteria in each control then hit the search button

4 surname
4 organization
4 program title
[two records returned]

2) put the following criteria in each control then hit the search button

[no records returned]
14 surname 14
14 organization 14
14 program title 14

Code behind search form:

Option Compare Database
Option Explicit

Private Sub cmdShowallRecords_Click()
Dim strSQL0 As String


Me.txtOrganization = ""
Me.txtProgramTitle = ""
Me.txtSurName = ""

strSQL0 = fSQL_SelectFrom & fWhere & fSurName & fOrganization & fProgramTitle
Me.DS.Form.RecordSource = Left(strSQL0, Len(strSQL0) - 5) & ")"

End Sub

'--------------------------------------------------

Private Sub cmdSearch_Click()
Dim strSQL0 As String

strSQL0 = fSQL_SelectFrom & fWhere & fSurName & fOrganization & fProgramTitle
       'MsgBox " >>> " & Left(strSQL0, Len(strSQL0) - 5) & ")"
Me.DS.Form.RecordSource = Left(strSQL0, Len(strSQL0) - 5) & ")"

End Sub


'--------------------------------------------------
Private Function fProgramTitle() As String

Dim strsubName As String
Dim strModuleName As String

strsubName = "fProgramTitle"
strModuleName = "Form - " & Me.Name

On Error GoTo Error_Handler:

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String

Dim strSTARL As String 
Dim strSTARR As String

strSTARL = Chr(34) & "*"        ' character 34 = "
strSTARR = "*" & Chr(34)        ' character 34 = "


'((tblMain.ProgramTitle) Like
'"*"
'me.txtProgramTitle]
'"*"
'") AND"

strSQL1 = "((tblMain.ProgramTitle) Like "
'"*"
'Me.txtProgramTitle
strSQL2 = ") AND "

strSQL0 = strSQL1 & strSTARL & Me.txtProgramTitle & strSTARR & strSQL2

fProgramTitle = strSQL0

Exit_ErrorHandler:
Exit Function

Error_Handler:
       Select Case Err.Number
              Case 1 'When required, replace place holder (1) with an error number
              MsgBox "Error produced by Place Holder please check your code ! Error Number >>> " _
              & Err.Number & " error Desc >> " & Err.Description, conappName
              
              Case Else
              MsgBox "Error From --- " & strModuleName & ", " & strsubName & " --- error Number >>> " & Err.Number _
              & " <<< Error Description >> " & Err.Description, , conappName
              
       End Select
Resume Exit_ErrorHandler

End Function  'fProgramTitle


'--------------------------------------------------
Private Function fOrganization() As String

Dim strsubName As String
Dim strModuleName As String

strsubName = "fOrganization"
strModuleName = "Form - " & Me.Name

On Error GoTo Error_Handler:

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSTARL As String
Dim strSTARR As String

strSTARL = Chr(34) & "*" ' character 34 = "
strSTARR = "*" & Chr(34) ' character 34 = "


'((tblMain.Surname) Like
'" * "
'me.txtOrganization)
'" * "
') AND


strSQL1 = "((tblMain.Organization) Like "
'" * "
'me.txtOrganization)
'" * "
strSQL2 = ") AND "

strSQL0 = strSQL1 & strSTARL & Me.txtOrganization & strSTARR & strSQL2 '& strSQL3 & strSQL4 & strSQL5 & strSQL6 & strSQL7 & strSQL8 & strSQL9

fOrganization = strSQL0 '" ((tblMain.Surname) Like " * " & me.txtSurname) AND "

Exit_ErrorHandler:
Exit Function

Error_Handler:
       Select Case Err.Number
              Case 1 'When required, replace place holder (1) with an error number
              MsgBox "Error produced by Place Holder please check your code ! Error Number >>> " _
              & Err.Number & " error Desc >> " & Err.Description, conappName
              
              Case Else
              MsgBox "Error From --- " & strModuleName & ", " & strsubName & " --- error Number >>> " & Err.Number _
              & " <<< Error Description >> " & Err.Description, , conappName
              
       End Select
Resume Exit_ErrorHandler

End Function  'fOrganization



'--------------------------------------------------



Private Function fWhere() As String

Dim strsubName As String
Dim strModuleName As String

strsubName = "fWhere"
strModuleName = "Form - " & Me.Name

On Error GoTo Error_Handler:

Dim strSQL0 As String
Dim strSQL1 As String

strSQL1 = " WHERE ("

strSQL0 = strSQL1

fWhere = strSQL0

Exit_ErrorHandler:
Exit Function

Error_Handler:
       Select Case Err.Number
              Case 1 'When required, replace place holder (1) with an error number
              MsgBox "Error produced by Place Holder please check your code ! Error Number >>> " _
              & Err.Number & " error Desc >> " & Err.Description, conappName
              
              Case Else
              MsgBox "Error From --- " & strModuleName & ", " & strsubName & " --- error Number >>> " & Err.Number _
              & " <<< Error Description >> " & Err.Description, , conappName
              
       End Select
Resume Exit_ErrorHandler

End Function  'fWhere



'--------------------------------------------------

Private Function fSurName() As String

Dim strsubName As String
Dim strModuleName As String

strsubName = "fSurName"
strModuleName = "Form - " & Me.Name

On Error GoTo Error_Handler:

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSTARL As String
Dim strSTARR As String

strSTARL = Chr(34) & "*" ' character 34 = "
strSTARR = "*" & Chr(34) ' character 34 = "

strSQL1 = "((tblMain.Surname) Like "
'*
'Me.txtSurname
strSQL2 = ") AND "

strSQL0 = strSQL1 & strSTARL & Me.txtSurName & strSTARR & strSQL2

fSurName = strSQL0 '" ((tblMain.Surname) Like " * " & me.txtSurname) AND "

Exit_ErrorHandler:
Exit Function

Error_Handler:
       Select Case Err.Number
              Case 1 'When required, replace place holder (1) with an error number
              MsgBox "Error produced by Place Holder please check your code ! Error Number >>> " _
              & Err.Number & " error Desc >> " & Err.Description, conappName
              
              Case Else
              MsgBox "Error From --- " & strModuleName & ", " & strsubName & " --- error Number >>> " & Err.Number _
              & " <<< Error Description >> " & Err.Description, , conappName
              
       End Select
Resume Exit_ErrorHandler

End Function  'fSurName


Private Function fSQL_SelectFrom() As String

Dim strsubName As String
Dim strModuleName As String

strsubName = "fSQL_SelectFrom" 'fSQL_SelectFrom is the stcok name, replace with fSQL_SelectFrom or something
strModuleName = "Form - " & Me.Name

On Error GoTo Error_Handler

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String



strSQL1 = "SELECT tblMain.ClientID, tblMain.Surname, tblMain.Organization, tblMain.ProgramTitle, tblMain.City, tblMain.State, tblMain.Zip4, tblMain.Telephone"
strSQL2 = " FROM tblMain"


strSQL0 = strSQL1 & strSQL2

fSQL_SelectFrom = strSQL0

Exit_ErrorHandler:
Exit Function

Error_Handler:
       Select Case Err.Number
              Case 1 'When required, replace place holder (1) with an error number
              MsgBox "Error produced by Place Holder please check your code ! Error Number >>> " _
              & Err.Number & " error Desc >> " & Err.Description, conappName
              
              Case Else
              MsgBox "Error From --- " & strModuleName & ", " & strsubName & " --- error Number >>> " & Err.Number _
              & " <<< Error Description >> " & Err.Description, , conappName
              
       End Select
Resume Exit_ErrorHandler

End Function  'sSQLX

Summary of the solution on the original post:
Two valuable solutions were offered to achieve a method of creating a dynamic search form.


To find the original post go to the forums under microsoft access discussion > queries> and search for the thread "Search Criteria" I can not post links at this time

1. Uncle gizmo's video tutorials:
   -build SQL string then set record source of subform to that string; uses a function to build SQL for each form control

2. Allen Brown

-build SQL string then set filter to the string; uses a procedure




tblMain (This is the data for table main in a tab delimited data format, data is in the field order shown in the table structure)

4 "4 Surname" "4 organization" "4 program title" "4 city" "4 state" "4 zip" "4 telephone"
5 "5 Surname" "5 organization" "5 program title" "5 city" "5 state" "5 zip" "5 telephone"
6 "6 surname" "6 organization" "6 program title" "6 city" "6 state" "6 zip" "6 telephone"
7 "7 surname" "7 organization" "7 program title" "7 city" "7 state" "7 zip" "7 telephone"
8 "8 surname" "8 organization" "8 program title" "8 city" "8 state" "8 zip" "8 telephone"
9 "Smith1" "234 organization" "234 program title" "234 city" "234 state" "234 zip" "234 telephone"
10 "smith2" "456 orgnaization" "456 program title" "456 city" "456 state" "456 zip" "456 telephone"
11 "1smith1" "11 organization" "11 program title" "111 city" "111 state" "111 zip" "111 telephone"
12 "2smith2" "22 organization" "22 program title" "22 city" "22 state" "22 zip" "22 telephone"
13 "1313 surname" "1313 organization" "1313 program title" "1313 city" "1313 state" "1313 zip" "1313 telephone"
14 "14 surname 14" "14 organization 14" "14 program title" "14 city 14" "14 state 14" "14 zip 14" "14 telephone 14"

 

[GinaWhipp]

Hmm, this is going to take a minute and my ribs are on the barbecue... so, if no one else tackles this I'll be back! Might come back even if someone does.

 

[DevTycoon]

To find the original post go to the forums under microsoft access discussion > queries> and search for the thread "Search Criteria" I can not post links at this time

1. Uncle gizmo's video tutorials:
   -build SQL string then set record source of subform to that string; uses a function to build SQL for each form control

2. Allen Brown

-build SQL string then set filter to the string; uses a procedure

Original post that prompted my question :
http://www.access-programmers.co.uk/forums/showthread.php?t=266241

Allen Brown's solution:
http://allenbrowne.com/ser-62.html

Uncle gizmo's solution: [video tutorial]
https://www.youtube.com/playlist?lis...AN0Jg9soJpBVvw

Sample DB is now attached

 

[vbaInet]

That's a very detailed post.

Did you make some changes to Allen Browne's code? On quick glance I can already see that the LIKE operator is not using the wildcard character (*). Without that it's not doing a wildcard search, but an equality (=) search.

 

[DevTycoon]

I posted the code inspired by Uncle Gizmo's method shown above. Allen Brown and Gizmo's code have the same outcome.


Also, I just experimented with your remarks and there is no change in my outcome. Are your referring to the syntax?:

Like "*" & [Forms]![Form1]![FormControlNameHere] & "*"

The SQL statement I used to test your hypothesis: (I fixed line breaks so code will not scroll far to right, original SQL was on 3 lines)

SELECT tblMain.ClientID, tblMain.Surname, tblMain.Organization, 
tblMain.Programtitle, tblMain.City, tblMain.State, tblMain.Zip4, 
tblMain.Telephone
FROM tblMain
WHERE (((tblMain.Surname) Like "*" & [Forms]![Form1]![txtSurName] & 
"*") AND ((tblMain.Organization) Like "*" & [Forms]![Form1]!
[txtOrganization] & "*") AND ((tblMain.Programtitle) Like "*" & [Forms]!
[Form1]![txtProgramTitle] & "*"));

SQL statement that is produced from my code:
note: sample database provides a message box of the sql statment produced by code, you just need to DE-comment the message box

SELECT tblMain.ClientID, tblMain.Surname, tblMain.Organization,
 tblMain.ProgramTitle, tblMain.City, tblMain.State, tblMain.Zip4,
 tblMain.Telephone FROM tblMain WHERE (((tblMain.Surname) Like "*14 
surname 14*") AND ((tblMain.Organization) Like "*14 organization 14*")
 AND ((tblMain.ProgramTitle) Like "*14 program title 14*"))

Example to reproduce bug: criteria is also an exact match to records in the database)

Correct me if I am wrong but these statements are giving me the same outcome because they are relatively the same SQL statement. They are just referencing the controls differently. ( I uploaded a new version of the database with a test query that shows our SQL is the same.)


Criteria input on form controls to test your SQL and my SQL statements:

• 14 surname 14
• 14 organization 14
• 14 program title 14

Example of how near match will return result:(near match to records in the database):

Criteria input on form controls

• 14 surname
• 14 organization
• 14 program title

I used the QBE query to test your hypothesis and typed in to the form controls the following values and am still experiencing this bug. If I remove the 14's from the end then I will get one record from your method and from my method. It seems that the bug still exists where wild cards do not allow an exact match to be found in SQL using your syntax or my syntax adopted from Uncle gizmo and Allen brown.

I get one record only when near match is used and not exact match. Ideally I would get 1 record in both searches:banghead::banghead::banghead:


Also, it seems that using the like operator does not solve this issue.

 

[vbaInet]

You must type super fast to give such a detailed response. If you can cut down the quicker we can resolve the issue.

If I enter "smith", "2" into the surname and organisation fields respectively, how many records do you expect to see and which ones?

 

[DevTycoon]

You must type super fast to give such a detailed response. If you can cut down the quicker we can resolve the issue.

If I enter "smith", "2" into the surname and organisation fields respectively, how many records do you expect to see and which ones?

I expect 2, client ID 9 & ID 12...

 

[vbaInet]

Ok. Try the attached.

 

[GinaWhipp]

Thanks vbaInet... nothing for me to do!

 

[DevTycoon]

Ok. Try the attached.

Seems to work nice. I do not see any changes in code...did I miss something?

 

[vbaInet]

Nope I didn't do anything to your code. Basically, whenever you write code you need to Debug > Compile, so I did that and I also performed a Compact & Repair. Compile is for your code, Compact and Repair is mainly for your database in general.

There seemed to be an inconsistency with the results when I closed and opened the form so it led me to take the above actions.

 

[DevTycoon]

Nope I didn't do anything to your code. Basically, whenever you write code you need to Debug > Compile, so I did that and I also performed a Compact & Repair. Compile is for your code, Compact and Repair is mainly for your database in general.

There seemed to be an inconsistency with the results when I closed and opened the form so it led me to take the above actions.

Well geez. I'm a noob. Thanks for helping me learn. This has been an experience in learning about how to use message boards and Access. I am very grateful for "vbaInet", 'GinaWhipp", and "Uncle Gizmo's" willingness to help.

 

[vbaInet]

We do our best to help.