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
| Private Sub Filtrer_Selection_Click()
Dim SQL As String
Dim dtDateMin As Date, dtDateMax As Date
Call GroupeDate_AfterUpdate
' **** ici il faut initialiser SQL ****
SQL = "SELECT
[Liste_Produit].[Date_Releve],[Liste_Produit].[Num_produit_Frais],[Liste_Produit].[Designation_ar],[Liste_Produit].[Nom_Type_Produit_ar],[Liste_Produit].[APPORT],[Liste_Produit].[Prix_Min],[Liste_Produit].[Prix_Fréq],[Liste_Produit].[Prix_Max] FROM
[Liste_Produit] WHERE (([Liste_Produit].[Num_produit_Frais]) Is Not Null) "
'Filtre [Jour]-----
If (Me.GroupeDate = Me.DTPicker_Day1.OptionValue) Then
SQL = SQL & " And
[Liste_Produit].[Date_Releve]=#" & Format(DTPicker_Day.Value, "mm/dd/yyyy") & "#"
'Filtre [Mois]-----
ElseIf (Me.GroupeDate = Me.DTPicker_Month1.OptionValue) Then
dtDateMin = DateSerial(Year(DTPicker_Month.Value), Month(DTPicker_Month.Value), 1)
dtDateMax = DateAdd("m", 1, dtDateMin) - 1
SQL = SQL & " And
[Liste_Produit].[Date_Releve] between #" & Format(dtDateMin, "mm/dd/yyyy") & "#" & _
" And #" & Format(dtDateMax, "mm/dd/yyyy") & "#"
'Filtre [Année]-----
ElseIf (Me.GroupeDate = Me.DTPicker_year1.OptionValue) Then
dtDateMin = DateSerial(Year(Me.DTPicker_year.Value), 1, 1)
dtDateMax = DateSerial(Year(Me.DTPicker_year.Value), 12, 31)
SQL = SQL & " And
[Liste_Produit].[Date_Releve] between #" & Format(dtDateMin, "mm/dd/yyyy") & "#" & _
" And #" & Format(dtDateMax, "mm/dd/yyyy") & "#"
'Filtre [user]---------
ElseIf Me.DTPicker_user11.Value < Me.DTPicker_user12.Value Then
dtDateMin = Me.DTPicker_user11.Value
dtDateMax = Me.DTPicker_user12.Value
SQL = SQL & " And
[Liste_Produit].[Date_Releve] between #" & Format(dtDateMin, "mm/dd/yyyy") & "#" & _
" And #" & Format(dtDateMax, "mm/dd/yyyy") & "#"
'Filtre [user]---------
ElseIf Me.DTPicker_user11.Value > Me.DTPicker_user12.Value Then
dtDateMin = Me.DTPicker_user12.Value
dtDateMax = Me.DTPicker_user11.Value
SQL = SQL & "And
[Liste_Produit].[Date_Releve] between #" & Format(dtDateMin, "mm/dd/yyyy") & "#" & _
" And #" & Format(dtDateMax, "mm/dd/yyyy") & "#"
End If
' Ajout clause ORDER BY
SQL = SQL & " Order by [Date_Releve] DESC;"
Me.lstProduit.RowSource = SQL
Me.lstProduit.Requery
End Sub |
Partager