Filtering Dates in a Subform with VBA not working

[rafaelsleao]

I'm trying to filter a subform based on the values of a combobox... The code looks like this:

  codigo = Me.codFerr
    descricao = Me.descFerr
    marca = Me.marcaFerr
    familia = Me.familiaFerr
    status = Me.statusFerr
    cert = Me.certificacao
    
    Select Case cert
    
        Case "SEM CERTIFICAÇÃO"
            strCertificacao = " AND ([prazoCert] Is Null)"
        Case "EM DIA"
            strCertificacao = " AND ([prazoCert] > Date() + 60)"
        Case "VENCIDO"
            strCertificacao = " AND ([prazoCert] <= Date())"
        Case "VENCE EM 30 DIAS"
            strCertificacao = " AND ([prazoCert] Between Date()+30 and Date())"
        Case "VENCE EM 60 DIAS"
            strCertificacao = " AND ([prazoCert] Between Date()+60 and Date()+30)"
    End Select
    
        
    strfilter = "[codFerr] Like '*" & codigo & _
    "*' AND [descFerr] Like '*" & descricao & _
    "*' AND [marcaFerr] Like '*" & marca & _
    "*' AND [familiaFerr] Like '*" & familia & _
    "*' AND [statusFerr] Like '*" & status & _
    "*'" & strCertificacao


    
    Me.subfrm.Form.Filter = strfilter
    Me.subfrm.Form.FilterOn = True

For some reason, the code works only when it's on "EM DIA", "VENCIDO" or "VENCE EM 60 DIAS".


If it is in "SEM CERTIFICAÇÃO" or "VENCE EM 30 DIAS" it does not show any value.

And yes, there are actual values in the subform that should be included in these categories.

Any ideas?

[Micron]

Try putting a break on he filteron line and then examine the filter. You could debug.print it if that helps, then you could copy it for the next step. If it looks correct, I'd create a test query on the table and add the filter as a Where clause. If you get no records for some but not others as before, the issue should be the filter. If you get records from the query but not the code, then I'd look deeper into the form and its code. What I would not do is us Between and have the later date come first. Also, you might find that the AND is null is the problem in some cases, if it should be OR.

[Welshgasman]

You should walk through the code with F8. Also Debug.Print the filter string.
Force cert to be one of the options not working and see what the filter produces.

It all looks OK to me, but obviously it is not.

That is how I get to the bottom of problems like this.

HTH

[rafaelsleao]

I did all that and didn't found anything abnormal.
One thing I realized is that the filtering of the dates works fine if do not have the rest of the filters.
Like if strfilter= "[prazoCert] Between Date()+60 and Date()+30"
If I use only that, the filter will work. I tried change the order of the filters, it did nothing...

[CJ_London]

suggest also check out the records you think should be returned, but are not - perhaps one of the other filters is excluding them

[rafaelsleao]

Just to check... When the filter is on "SEM CERTIFICAÇÃO" the value of the filter string is:
"[codFerr] Like '**' AND [descFerr] Like '**' AND [marcaFerr] Like '**' AND [familiaFerr] Like '**' AND [statusFerr] Like '**' AND ([prazoCert] Is Null)"

When on "ATÉ 30 DIAS" is:

"[codFerr] Like '**' AND [descFerr] Like '**' AND [marcaFerr] Like '**' AND [familiaFerr] Like '**' AND [statusFerr] Like '**' AND ([prazoCert] Between Date()+30 and Date())"

Both of the above don't work.

When on "ATE 60 DIAS" is:
"[codFerr] Like '**' AND [descFerr] Like '**' AND [marcaFerr] Like '**' AND [familiaFerr] Like '**' AND [statusFerr] Like '**' AND ([prazoCert] Between Date()+60 and Date()+30)"
This one works fine.

[rafaelsleao]

Thanks AJAX, you were write. There was nothing wrong with the date filters. The problem was with the marcaFerr field.

AND [marcaFerr] Like '**'

This does not accept Null values. That was the issue. There was a coincidence where all "SEM CERTIFICAÇÃO" and "EM ATÉ 30 DIAS" had Null values for the marcaFerr field and it was not showing.
I fixed the code, ending up like this.

codigo = Me.codFerr
    descricao = Me.descFerr
    marca = Me.marcaFerr.Value
    familia = Me.familiaFerr
    status = Me.statusFerr
    cert = Me.certificacao
    
    If marca <> "" Then
        
        marca = "*' AND [marcaFerr] Like '*" & marca


    End If
    
    
    Select Case cert
    
        Case "SEM CERTIFICAÇÃO"
            strCertificacao = " AND ([prazoCert] Is Null)"
        Case "EM DIA"
            strCertificacao = " AND ([prazoCert] > Date() + 60)"
        Case "VENCIDO"
            strCertificacao = " AND ([prazoCert] <= Date())"
        Case "VENCE EM 30 DIAS"
            strCertificacao = " AND ([prazoCert] Between Date() and Date()+30)"
        Case "VENCE EM 60 DIAS"
            strCertificacao = " AND ([prazoCert] Between Date()+30 and Date()+60)"
    End Select
    
        
    strfilter = "[codFerr] Like '*" & codigo & _
    "*' AND [descFerr] Like '*" & descricao & _
    marca & _
    "*' AND [familiaFerr] Like '*" & familia & _
    "*' AND [statusFerr] Like '*" & status & _
    "*'" & strCertificacao
    
'    strfilter = "[prazoCert] is Null"
    
    Me.subfrm.Form.Filter = strfilter
    Me.subfrm.Form.FilterOn = True

suggest also check out the records you think should be returned, but are not - perhaps one of the other filters is excluding them