Solved Filtering a table on different date fields using a parameter for the name of the field (1 Viewer)

Etxezarreta

Member
Local time
Today, 08:36
Joined
Apr 13, 2020
Messages
168
Hello everyone,
I need to filter a table from a form: depending on the choice of option buttons, the table will be fildterd on two different date fields:
- field named" DateReceptionAchat" if the option button 1 is picked, "eDateReellereception " is the corresponding name in my enumaration (
Code:
Public Enum eOptions
    eDateReellereception = 1
    eDatePreviReception
End Enum

-else, field named "Date_PrevireceptionAchat"
I create a "SELECT FROM WHERE "string depending on the choice of the option button, then this string becomes the recordsource of the form.
The problem is that when the form refreshes, it doesnt display the correct data. I tried to change the data type of date_Date, but I am going nowhere.
Would yu have any suggestion please?
Many thanks in advance.
Etxe

Sub TrisConditionnelsFormulaire()

Code:
Dim str_Sql As String
Dim int_CritereAnnee As Integer
Dim int_CritereMois As Integer
Dim int_Nom As Integer
Dim date_Date As Date

Dim str_AllCriteria As String

Me.RecordSource = "t_SaisieReceptionAchats"

If Not IsNull(Me.combo_FiltreAnnee) Then int_CritereAnnee = Me.combo_FiltreAnnee
If Not IsNull(Me.combo_FiltreMois) Then int_CritereMois = Me.combo_FiltreMois
If Not IsNull(Me.combo_FiltreNom) Then int_Nom = Me.combo_FiltreNom

If Me.cadDate.Value = eDateReellereception Then 'cadDate is the option button group
    date_Date = DateReceptionAchat
Else
    date_Date = Date_PrevireceptionAchat
End If

    If int_CritereAnnee > 0 Then
        str_AllCriteria = str_AllCriteria & IIf(Len(str_AllCriteria) > 0, " AND ", "") & "(Year(#" & Format(date_Date, "mm\/dd\/yyyy") & "# )) = (" & int_CritereAnnee & ")"
    End If

    If int_CritereMois > 0 Then
        str_AllCriteria = str_AllCriteria & IIf(Len(str_AllCriteria) > 0, " AND ", "") & "(Month(#" & Format(date_Date, "mm\/dd\/yyyy") & "# )) = (" & int_CritereMois & ")"
      End If
    
    If int_Nom > 0 Then
        str_AllCriteria = str_AllCriteria & IIf(Len(str_AllCriteria) > 0, " AND ", "") & " Fk_SousComposant = " & int_Nom & ""
    End If

    If str_AllCriteria = "" Then
        Me.RecordSource = "t_SaisieReceptionAchats"
        Me.Refresh
    Else
        str_Sql = "SELECT DISTINCTROW *" _
                    & " FROM t_SaisieReceptionAchats" _
                    & " WHERE " & str_AllCriteria & ""
        Me.RecordSource = str_Sql
        Me.Refresh
    End If


End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:36
Joined
Oct 29, 2018
Messages
17,177
Hi. Have you tried simply using the form's Filter property?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:36
Joined
Feb 19, 2002
Messages
33,777
You probably don't need the .Refresh since replacing the RecordSource should REQUERY the form.

Put a stop in the code and print out str_sql before you modify the RecordSource. Copy the string from the immediate window and put it in a new query. Run it and determine if it actually returns the records you are looking for.
 

Etxezarreta

Member
Local time
Today, 08:36
Joined
Apr 13, 2020
Messages
168
Hi,
Thank you DB Guy and Pat.
I just found out: the problem was linked to the names of the fields in the Year and Month formulas: it must be a string.
Here is the correct code:

Code:
Sub TrisConditionnelsFormulaire()


Dim str_Sql As String
Dim int_CritereAnnee As Integer
Dim int_CritereMois As Integer
Dim int_Nom As Integer
Dim str_Date As String


Dim str_AllCriteria As String


Me.RecordSource = "t_SaisieReceptionAchats"


If Not IsNull(Me.combo_FiltreAnnee) Then int_CritereAnnee = Me.combo_FiltreAnnee
If Not IsNull(Me.combo_FiltreMois) Then int_CritereMois = Me.combo_FiltreMois
If Not IsNull(Me.combo_FiltreNom) Then int_Nom = Me.combo_FiltreNom


If Me.cadDate.Value = eDateReellereception Then
    str_Date = "DateReceptionAchat"
Else
    str_Date = "Date_PrevireceptionAchat"
End If


If int_CritereAnnee > 0 Then
    str_AllCriteria = str_AllCriteria & IIf(Len(str_AllCriteria) > 0, " AND ", "") & "(Year(" & str_Date & ")) = (" & int_CritereAnnee & ")"
End If


If int_CritereMois > 0 Then
    str_AllCriteria = str_AllCriteria & IIf(Len(str_AllCriteria) > 0, " AND ", "") & "(Month(" & str_Date & "))= (" & int_CritereMois & ")"
End If


If int_Nom > 0 Then
    str_AllCriteria = str_AllCriteria & IIf(Len(str_AllCriteria) > 0, " AND ", "") & " Fk_SousComposant = " & int_Nom & ""
End If


If str_AllCriteria = "" Then
    Me.RecordSource = "t_SaisieReceptionAchats"
    Me.Refresh
Else
    str_Sql = "SELECT DISTINCTROW *" _
                & " FROM t_SaisieReceptionAchats" _
                & " WHERE " & str_AllCriteria & ""
    Debug.Print str_Sql
    Me.RecordSource = str_Sql
    Me.Refresh
End If


End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:36
Joined
Oct 29, 2018
Messages
17,177
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom