1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90
| Dim strTable As String, strField As String, strCriteria As String, strSql As String
Dim Criter As Variant
Dim intTypChamp As Integer
Dim intOpeChamp As Integer
strTable = Me.cbo_table ' recupère le nom de la table
strField = Me.cbo_champ ' recupère le nom du champ
' compose le critere de recherche
Select Case Me.opt_Recherche
intTypChamp = lf_GetTypeField(strTable, strField) ' pour trouver le type du champs ...
intOpeChamp = Me.opt_Recherche
Select Case intTypChamp
Case dbBoolean ' bool
If intOpeChamp = 1 Then ' oui
strCriteria = strTable & "." & strField & "=-1"
ElseIf intOpeChamp = 2 Then ' non
strCriteria = strTable & "." & strField & "=0"
Else
strCriteria = strTable & "." & strField & "=Null"
End If
Case dbByte To dbBinary, dbLongBinary, dbBigInt To dbVarBinary, dbNumeric To dbTimeStamp
' traite les numeriques
strCriteria = Me.txt_critere
' traite la virgule si elle existe
If InStr(1, Me.txt_critere, ",") > 0 Then strCriteria = Replace(Me.txt_critere, ",", ".", 1)
' pour les versions antérieure à la 2000
'If InStr(1, Me.txt_critere, ",") > 0 Then _
' strCriteria = Left(Me.txt_critere, InStr(1, Me.txt_critere, ",") - 1) _
' & "." & Right(Me.txt_critere, InStr(1, Me.txt_critere, ","))
If intTypChamp = dbDate And IsDate(Me.txt_critere) Then strCriteria = "#" _
& Me.txt_critere & "#" ' type champ = date
' rajoute les dièses
If Not IsNull(Me.txt_critere) Then
Select Case intOpeChamp ' numerique, date
Case 1 ' =
strCriteria = strTable & "." & strField & "=" & strCriteria
Case 2 ' >=
strCriteria = strTable & "." & strField & ">=" & strCriteria
Case 3 ' <=
strCriteria = strTable & "." & strField & "<=" & strCriteria
Case 4 '<>
strCriteria = strTable & "." & strField & "<>" & strCriteria
End Select
End If
Case dbText, dbMemo, dbChar ' texte
Select Case intOpeChamp
Case 5 ' ne contient pas
strCriteria = "NOT (" & strTable & "." & strField & " Like ""*" & strCriteria & "*"")"
End Select
Case Else
MsgBox "Cas non prévu."
Exit Sub
End Select
' construit la requête sql
If Me.Opt_RechCourante And Not Len(Me.lst_resultat.RowSource) = 0 Then
If Not Me.lst_resultat.RowSource Like "*FROM " & strTable & "*" Then
MsgBox "La recherche précédente ne porte pas sur la même table que la recherche actuelle.", _
vbExclamation + vbOKOnly, "Erreur"
Exit Sub
End If
strSql = Left(Me.lst_resultat.RowSource, Len(Me.lst_resultat.RowSource) - 3)
strSql = strSql & " AND " & strCriteria & "));"
Else
' construit la rq sql
strSql = "SELECT DISTINCTROW " & strTable & ".*"
strSql = strSql + " FROM " & strTable
strSql = strSql + " WHERE ((" & strCriteria & "));"
End If
Me.lst_resultat.RowSource = strSql ' affecte sql a lst_Resultat
Me.lst_resultat.Requery ' recalcule la liste
End Sub |
Partager