Solved Link a subreport based on a date range (1 Viewer)

[zelarra821]

I have a report created for the VAT Book, where the list of invoices appears, with the tax base, the VAT fee, and the amount. This is the report:

Now, I need to add to that report a summary of each of the VATs, because in the same invoice there may be items with different VATs. So:

10% -> X €

21% -> X €

That summary has to go in a subreport, so that if, in the future, I have to add new ones, they appear automatically. However, I find that I have to relate the main report to the subreport using the date range I choose, because it is what determines which records appear and which do not. And this is where the problem arises, how do I link them?

I choose the dates in a form, which filters the report according to this code:

Private Sub CmdAbrirInforme_Click()
    If Not IsNull(Me.txtDesdeF) And Not IsNull(Me.txtHastaF) Then
        Call ElFiltroFecha(Me, "[Fecha]", "ILibroDeIVA")
        DoCmd.Close acForm, Me.Name
    Else
        MsgBox "Es necesario introducir las dos fechas.", vbInformation, "Gestión de facturas y presupuestos"
    End If
End Sub
Public Function ElFiltroFecha(ElForm As Form, ElCampo As String, ElInforme As String) As String
    Dim MiArgumento As String
    ElFiltroFecha = ElCampo & " BETWEEN #" & Format(Nz(ElForm.txtDesdeF, #1/1/1900#), "mm/dd/yyyy") & "# AND #" & _
                               Format(Nz(ElForm.txtHastaF, #12/31/9999#), "mm/dd/yyyy") & "#"
    MiArgumento = " - Del " & Year(ElForm.txtDesdeF) & " " & Format(ElForm.txtDesdeF, "mm") & " " & Format(ElForm.txtDesdeF, "dd") & " hasta el " & _
                    Year(ElForm.txtHastaF) & " " & Format(ElForm.txtHastaF, "mm") & " " & Format(ElForm.txtHastaF, "dd")
    DoCmd. OpenReport ElInforme, acViewPreview,, ElFiltroFecha,, MiArgumento
End Function

How do I have the database mounted

Right now I have two tables for invoices. In the main one, I add the general data, such as the InvoiceCode, Date, CustomerCode; and in the secondary, the Concept, Invoice Code (to link it with the main one), Quantity, Price, VAT Code. The reason? Because I need to add more than one item per invoice. Also, each item can have a different VAT, and I cannot generalize one for the entire invoice.

Solution I had thought

I had thought of doing it through a formula, with DSum. But that would imply that, if I have to add a new VAT tomorrow, I would have to do it manually (add another formula with that new VAT). And this database is not going to be for me. For this reason, I need a subreport.

Thanks a lot.

 

[arnelgp]

you create a query against your table, then create a subform out of it:

SELECT FormatPercent(Round([CoutalVA]/[Base Imponible],2),0) AS Expr1, Sum(salesTable.CoutalVA) AS TotalVAT
FROM salesTable
GROUP BY FormatPercent(Round([CoutalVA]/[Base Imponible],2),0);

 

[zelarra821]

I know how to create the query. However, I can't link report to subreport, because I filter the report by two dates

 

[zelarra821]

I want to know how to get the same registers in the report and subreport

 

[zelarra821]

Solution:

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "Select * from CLibroDeIVASubinforme where " & Me.Parent.Filter
End Sub