Search Query with both numbers and text.

[Mirador]

Hi and thanx for reading my post..

I got this search form which uses this SQL query to search for matches in the Person form. it's absolutely neccesary to search both Number-fields and Text-fields... This is a sample of the query :


SELECT * FROM Person WHERE SSNr LIKE '%" & Me.txtSearch & "%'" & _
" OR Surname LIKE '%" & Me.txtSearch & "%'" & _
" OR DUFNr LIKE '%" & Me.txtSearch & "%'" & _
" OR Address LIKE '%" & Me.txtSearch & "%'" & _
" OR Nationality LIKE '%" & Me.txtSearch & "%'" & _
" OR Passnr LIKE '%" & Me.txtSearch & "%'" & _
" OR Opprinnelses adresse LIKE '%" & Me.txtSearch & "%'"

What i wonder about is :

How can i "modify" this query so that the app. dont crash because of mismatch in Numbers/Text values in the txtSearch field ? Any errorhandling or something ?

Altso wondering about how i treat "spaces" in the Query (ex : Opprinnelses adresse)...

 

[dcx693]

I've never tried doing a search the way you are. It's very all-encompassing and can yield undesired results....but it is your query after all. I would search for particular values within particular fields.

Second, you are delimiting all your fields with both the ' and the % symbols. It's redundant, but in addition, using the "%" to delimit text and numbers is not a good idea. As I recall, the % symbol is an undocumented delimiter. If you are trying to search for any type of match (since I see you are using the LIKE operator) you probably mean to use the * symbol.

I would guess that all of your fields are text, and I would change it to this:
SELECT * FROM Person WHERE SSNr LIKE '*" & Me.txtSearch & "'*" & _
" OR Surname LIKE '*" & Me.txtSearch & "'*" & _
" OR DUFNr LIKE '*" & Me.txtSearch & "'*" & _
" OR Address LIKE '*" & Me.txtSearch & "'*" & _
" OR Nationality LIKE '*" & Me.txtSearch & "'*" & _
" OR Passnr LIKE '*" & Me.txtSearch & "'*" & _
" OR Opprinnelses adresse LIKE '*" & Me.txtSearch & "'*"

Also, the LIKE operator cannot be used with numbers. That's another reason I suspect all your fields are text.

 

[Mirador]

Hi and thanx for your reply

Thanx for the tip with the *'s. Will try that out..

but half of the fields are number fields i'm afraid.. That's why i'm getting problems executing the query, and it results in a operator error, which is because it's trying to search the text-fields with number value, and number-fields with text values..

Hmm.. i have made better search methods, but the problem is that this is kind of a "dump" register database for criminal investigation, and they need to search _EVERYTHING_ in the database, (person is only 1 of 6 forms) with maybe ex a car-registration number. There's lots of "Comment", "More info.." fields in the forms which might contain both numbers and letters.

Anyone got any good idea how to search for everything they way i have done, but change it so it dont crash when i search ?

Terje.

I've never tried doing a search the way you are. It's very all-encompassing and can yield undesired results....but it is your query after all. I would search for particular values within particular fields.

Second, you are delimiting all your fields with both the ' and the % symbols. It's redundant, but in addition, using the "%" to delimit text and numbers is not a good idea. As I recall, the % symbol is an undocumented delimiter. If you are trying to search for any type of match (since I see you are using the LIKE operator) you probably mean to use the * symbol.

I would guess that all of your fields are text, and I would change it to this:
SELECT * FROM Person WHERE SSNr LIKE '*" & Me.txtSearch & "'*" & _
" OR Surname LIKE '*" & Me.txtSearch & "'*" & _
" OR DUFNr LIKE '*" & Me.txtSearch & "'*" & _
" OR Address LIKE '*" & Me.txtSearch & "'*" & _
" OR Nationality LIKE '*" & Me.txtSearch & "'*" & _
" OR Passnr LIKE '*" & Me.txtSearch & "'*" & _
" OR Opprinnelses adresse LIKE '*" & Me.txtSearch & "'*"

Also, the LIKE operator cannot be used with numbers. That's another reason I suspect all your fields are text.

 

[dcx693]

The way you are searching is very inexact, but like I said, it's your query. The question now is how to search the fields properly. If you try to search for a text value in a numeric field, for example, you'll get a "Data type mismatch in criteria expression" error.

There are a few ways around this. One is to search for the proper data in the proper fields, but you don't want to do that. Another way is to use the Cstr() function when you are trying to search a text field. For example, something like this:

SELECT * FROM Person WHERE SSNr =" Me.txtSearch & "'*" & _
" OR Surname LIKE '*" & Me.txtSearch & "'*" & _
" OR DUFNr LIKE '*" & Cstr(Me.txtSearch) & "'*" & _
" OR Address LIKE '*" & Cstr(Me.txtSearch) & "'*" & _
" OR Nationality LIKE '*" & Cstr(Me.txtSearch) & "'*" & _
" OR Passnr Like '*" & Cstr(Me.txtSearch) & "'*" & _
" OR Opprinnelses adresse LIKE '*" & Cstr(Me.txtSearch) & "'*"

That assumes that SSNr is numeric, and that in some of the fields, you might be searching for strings that look like numbers. I assume that you won't have numbers in the Surname field.

 

[Mirador]

Thanx a LOT !!.. for your help

It was most appreciated and ... IT WORKED !§!!!!

But.. it seems it dont like the fieldnames which got spaces inbetween like this :

" OR Opprinnelses adresse LIKE '*" & Cstr(Me.txtSearch)

Hmm... any way to format this to get it to work ?

Tejre.

The way you are searching is very inexact, but like I said, it's your query. The question now is how to search the fields properly. If you try to search for a text value in a numeric field, for example, you'll get a "Data type mismatch in criteria expression" error.

There are a few ways around this. One is to search for the proper data in the proper fields, but you don't want to do that. Another way is to use the Cstr() function when you are trying to search a text field. For example, something like this:

SELECT * FROM Person WHERE SSNr =" Me.txtSearch & "'*" & _
" OR Surname LIKE '*" & Me.txtSearch & "'*" & _
" OR DUFNr LIKE '*" & Cstr(Me.txtSearch) & "'*" & _
" OR Address LIKE '*" & Cstr(Me.txtSearch) & "'*" & _
" OR Nationality LIKE '*" & Cstr(Me.txtSearch) & "'*" & _
" OR Passnr Like '*" & Cstr(Me.txtSearch) & "'*" & _
" OR Opprinnelses adresse LIKE '*" & Cstr(Me.txtSearch) & "'*"

That assumes that SSNr is numeric, and that in some of the fields, you might be searching for strings that look like numbers. I assume that you won't have numbers in the Surname field.

 

[Mile-O]

But.. it seems it dont like the fieldnames which got spaces inbetween like this :

" OR Opprinnelses adresse LIKE '*" & Cstr(Me.txtSearch)

Any names with spaces have to be surrounded with square brackets.

[Opprinnelses adresse]

 

[Clueless Newbie]

" OR Address LIKE '*" & Cstr(Me.txtSearch) & "'*"

Interesting thread. In a similar function, I'm using

" OR  (nz(Address,'')) like '*" & Me!txtSearch & "*'"

instead of the solution posted above. It seems to work fine for numbers as well as text and date fields. Can anyone tell me what the advantages of dcx693's solution are, please? I'm sure there's room for improvement in my project.

 

[Mirador]

Got it fixed.. at last..

Tablename.[Field Name] worked..

Thanx a LOT !!.. for your help

It was most appreciated and ... IT WORKED !§!!!!

But.. it seems it dont like the fieldnames which got spaces inbetween like this :

" OR Opprinnelses adresse LIKE '*" & Cstr(Me.txtSearch)

Hmm... any way to format this to get it to work ?

Tejre.

 

[dcx693]

Interesting thread. In a similar function, I'm using

" OR  (nz(Address,'')) like '*" & Me!txtSearch & "*'"

instead of the solution posted above. It seems to work fine for numbers as well as text and date fields.

I'm not sure exactly what your code snippet is trying to do. You're taking a field from a table/query, testing to see if it's Null, and replacing it with an empty string if it is Null. You are then testing to see if it's Like a search string. If the Address is numeric though, how does this work? Don't you get a data type mismatch error?

 

[Clueless Newbie]

If the Address is numeric though, how does this work? Don't you get a data type mismatch error?

Nope, I don't. I have to admit that my query was a lot of "trial and error". It took me a while to get it formated in such a way that it worked. That's why I was wondering whether it can be improved in any way. I have a textfield ("suchkrit") containing the search string in my form "Datenerfassung". On click of the search button I open another form ("SYSSuche"), which then performs the actual search and displays the rows of data found. When I double-click any of those rows, the recordset gets displayed in "Datenerfassung" and "SYSSuche" is automatically closed. If the user leaves "suchkrit" empty, "SYSSuche" will display all recordsets at once.

Here's the search sub including the whole query:

Private Sub Form_Open(Cancel As Integer)

  On Error GoTo form_open_Err:

                                        
    strsql = "SELECT Daten.id , Daten.kunde as Kunde , Daten.KundenNr, format(Vertragsnummern.VertragsNr,""0"") as VertragNr, Daten.Vertragsinhalt from Daten LEFT JOIN Vertragsnummern ON Daten.ID = Vertragsnummern.ID WHERE kunde like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz(KundenNR,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(SAMKundenNr,'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz(Vertragsnummern.VertragsNR,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(AngebotNR,'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz(AuftragNR,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(Vertragsinhalt,'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz(EingepflegtAm,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(NachfrageOriginalBeiWem,'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz(AntwortOriginalAm,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(Beginn,'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz(Ende,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(Vertragsart,'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz([Verlängerung],'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz([KündDatum],'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR (nz([KündFrist],'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(Division,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(Profitcenter,'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz(AM,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(Nachfrage1Dat,'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz(Nachfrage1BeiWem,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(Anwort1Am,'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz(Nachfrage2Dat,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(Nachfrage2BeiWem,'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz(Anwort2Am,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(Nachfrage3Dat,'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz(Nachfrage3BeiWem,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(Anwort3Am,'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz(ZurErfassungAm,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(VonErfassungAm,'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz(ZumScannen,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(GescanntAm,'')) like '*" & Form_Datenerfassung!suchkrit & "*' OR  (nz(AblageFileNetAm,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(AblageOriginalAm,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(AngelegtVon,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(ZurErfassungBereit,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(AblageOriginalAm,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " OR  (nz(BestellNr,'')) like '*" & Form_Datenerfassung!suchkrit & "*'" & _
                                            " ORDER BY Kunde"
                                            
                                            

   
    Me!Listbox1.RowSource = strsql
    Me!Listbox1.ColumnCount = 5
    Me!Listbox1.ColumnWidths = "0;3000;1000;1000;3000"
   
       
    Me!Listbox1.SetFocus
    SendKeys "{DOWN}"
         Debug.Print "O:"; Me!Listbox1.ListCount
         If Me!Listbox1.ListCount = 0 Then
         MsgBox "Es wurden keine entsprechenden Datensätze gefunden."
         DoCmd.Close A_FORM, "SYSSUCHE"
         End If

  Exit Sub

form_open_Err:
  MsgBox "Folgender Fehler trat auf: " & Error$
  Resume Next

End Sub