Solved A subreport does not filter by date

[zelarra821]

Hi there.

I have a dialog to choose the date for which I want it to show me a list of the invoices for that period and a summary in a subreport in which it breaks down the VAT.

What I do is filter by date both the main report and the subreport. The subreport filters it in a hidden way. However, it does not get the result for all the dates.

You can try with the date August 21, 2020 (8-21-20). On that date, you should get 21% VAT nothing more, and both (10 and 21) come out.

I leave you the database.

What am I doing wrong? Could you do it another way?

 

[CJ_London]

looks to me like you are using a date format of dd-mm-yyyy. You need to use mm/dd/yyyy or yyyy-mm-fdd when referencing a date in sql. You also need to surround with the # character so sql knows that what is between is a date

 

[zelarra821]

Can not be. I am using a function from @MajP, which works for the main report, how is it not going to work for the subreport?

 

[zelarra821]

Fecha BETWEEN #08/21/2020# AND #08/21/2020#

 

[zelarra821]

I have already solved. Thanks for the help.

 

[MajP]

1. ALWAYS put option explicit (require declaration) in all modules.
This code would never ever work and you would never know why

Public Function ElFiltroFecha(ElForm As Form, ElCampo As String, ElInforme As String, Optional Subinforme As String, _
Optional Recibidas As Boolean) As String
Dim MiArgumento As String
 
    If Not IsNull(ElForm.txtDesdeF) And Not IsNull(ElForm.txtHastaF) Then
 
        ElFiltroFecha = GetBetweenFilter(ElForm.txtDesdeF, ElForm.txtHastaF, ElCampo)
 
        MiArgumento = "Del " & Format(ElForm.txtDesdeF, "dd-mm-yy") & " hasta el " & _
                        Format(ElForm.txtHastaF, "dd-mm-yy")
                     
        Debug.Print "ElFiltroFech " & ElFiltroFecha
        If Subinforme <> "" Then
            DoCmd.OpenReport Subinforme, acViewDesign, , ElFitroFecha, acHidden
            DoCmd.Close acReport, Subinforme, acSaveYes
        End If
     
        DoCmd.OpenReport ElInforme, acViewPreview, , ElFiltroFecha, , MiArgumento
        DoCmd.Close acForm, ElForm.Name
    Else
        MsgBox "Es necesario introducir las dos fechas.", vbInformation
    End If
End Function

ElfiltroFecha vs ElFitroFecha

2. This is a horrible idea
" DoCmd.OpenReport Subinforme, acViewDesign, , ElFitroFecha, acHidden"
NEVER EVER EVER open to design view at runtime. Never will work in a compiled program.
Pass the filter to the Main form in open args. If you need two sets of open args then pass the arguments in seperated by a semicolon
miargumento & "; " & ElfiltroFecha

in the main forms on open split these
Me.caption = Split(me.openargs)(0)
me.subformName.form.filter = split(me.openargs)(1)