ACCESS VBA - Consultations with several periods

Question

Good afternoon!

I’m creating a chart query form. Inside the form I have some comboboxes store a string in a variable and when the client clicks to filter this string is inserted inside the "WHERE" and filters the records of my subform.

I need to create a record query by period (month, previous month, quarter, semester, year).

I know very little of access and vba, so the solution I thought was the following: I created a table with the periods I want to consult and in my form I created a combobox to select. When the client selects "month" in the combobox the filter would add in "WHERE" something like (Year([date])=Year(Now()) AND Month([date])=Month(Now()). However it is returning type error (13 - Incompatible types). I believe it has to do with date formatting and that the syntax in vba is different from the criteria of a query in design mode.

Can anyone help me? Follow the code I’m creating:

Private Sub cmdFilter_Click()

    Dim strWhere As String                  'String com o critério.
    Dim lngLen As Long                      'Comprimento da string do critério para acrescentar.
 
    '***********************************************************************
    'Olhe para cada combobox e construa a string com os critérios a partir das que não estão em branco.
    '***********************************************************************
    
    If Not IsNull(Me.cboEmpresa) Then
        strWhere = strWhere & "([t_grupo_ID] = " & Me.cboEmpresa & ") AND "
    End If

    If Not IsNull(Me.cboSetores) Then
        strWhere = strWhere & "([t_empresas_ID] = " & Me.cboSetores & ") AND "
    End If

    If Not IsNull(Me.cboReceitas) Then
    strWhere = strWhere & "([t_cat_entradas_saidas_ID] = " & Me.cboReceitas & ") AND "
    End If

    If Not IsNull(Me.cboPeriodos) Then
    If Me.cboPeriodos = 1 Then strWhere = strWhere & "((Year([data])=Year(Now()) AND Month([data])=Month(Now())))" And ""
    End If
    

    'Verifica se a string tem mais que 5 caracteres para remover.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Não tem informação na string.
        MsgBox "Selecione algum critério", vbInformation, ""
    Else                    'existe critério selecionado e remova o " AND " no final.
        strWhere = Left$(strWhere, lngLen)
        
        'insira a string no filtro do formulário.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub ```

Answer 1

To whom it may interest... I have solved my problem with date formatting. Follows code:

If Not IsNull(Me.cboPeriodos) Then
    If Me.cboPeriodos = 1 Then
        strWhere = strWhere & "Format(dt_reg,'yyyy-mm')=Format(date(),'yyyy-mm') And "
    ElseIf Me.cboPeriodos = 2 Then
        strWhere = strWhere & "Format(dt_reg, 'mm yyyy')=Format(DateSerial(Year(date()), Month(date()) - 1, 1), 'mm yyyy') And "
    ElseIf Me.cboPeriodos = 3 Then
        strWhere = strWhere & "Format(dt_reg, 'mm yyyy') between Format(DateSerial(Year(Date()), Month(Date()) - 3, 3),'mm yyyy') and Format(date(),'yyyy-mm') And "
    ElseIf Me.cboPeriodos = 4 Then
        strWhere = strWhere & "Format(dt_reg, 'mm yyyy') between Format(DateSerial(Year(Date()), Month(Date()) - 6, 6),'mm yyyy') and Format(date(),'yyyy-mm') And "
    ElseIf Me.cboPeriodos = 5 Then
                strWhere = strWhere & "Year(dt_reg) = Year(date()) And "
    ElseIf Me.cboPeriodos = 6 Then
        strWhere = strWhere & "format(dt_reg, 'yyyy') = Format(DateAdd('yyyy', -1, Date()), 'yyyy') And "
    ElseIf Me.cboPeriodos = 7 Then
        strWhere = strWhere & "format(dt_reg, 'yyyy') = Format(DateAdd('yyyy', -2, Date()), 'yyyy') And "
    ElseIf Me.cboPeriodos = 8 Then
        strWhere = strWhere & "format(dt_reg, 'yyyy') = Format(DateAdd('yyyy', -3, Date()), 'yyyy') And "
        
    End If
End If
    

    Me.Filter = strWhere
    Me.FilterOn = True
    Me.OrderBy = "dt_reg DESC"
    Me.OrderByOn = True
    Me.Refresh