Hi All,
I am new to this forum and new to VBA n SQL skill set. Need your advice and direct me to the right path if i am wrong.
As a part of my case study, I got a scenario to produce the Report with corresponding information attached in the screenshot (kindly check the format).
I need to display the year, Date, agent name, amount field, note and sub total for each year.
I need to write the SQL query code in VBA and i designed the report with proper field positions.
YEAR column: Display Value only once for the FIRST ROW for Maximum value of Date field value for each set of YEAR. If it has only one record, it display that year.
SUB TOTAL Column: Display Total Value for Amount field only once for the LAST ROW of each set of year record (Mininum value of year).
How to make it available the values for first and last record alone ?
I just wrote my SQL code based on your inputs in my editor..
Based on the input from Plog (from this forum), i wrote a code which listed below.
While saving it, it shows the code in RED COLOR due to compilation error. Something was wrong as I have no idea.. Any thought on this.
As I am writing the query in Me.Recordsource = "SQL query", where i should place the below listed code in the query as per Plog???
Change the Year Control Source to this:
=IIf([GroupPos]=1,[Year])
Change the GroupTotal Control Source to this:
=IIf([GroupPos]=[GroupSize],[GroupTotal])
If this is not possible in Me.recordsource, then its advisable to use recordset like writing in two different queries.
Please advise me experts ... Correct me if i am wrong in my way..
I am new to this forum and new to VBA n SQL skill set. Need your advice and direct me to the right path if i am wrong.
As a part of my case study, I got a scenario to produce the Report with corresponding information attached in the screenshot (kindly check the format).
I need to display the year, Date, agent name, amount field, note and sub total for each year.
I need to write the SQL query code in VBA and i designed the report with proper field positions.
YEAR column: Display Value only once for the FIRST ROW for Maximum value of Date field value for each set of YEAR. If it has only one record, it display that year.
SUB TOTAL Column: Display Total Value for Amount field only once for the LAST ROW of each set of year record (Mininum value of year).
How to make it available the values for first and last record alone ?
I just wrote my SQL code based on your inputs in my editor..
Based on the input from Plog (from this forum), i wrote a code which listed below.
Code:
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT CessioneCredito.Anno, CessioneCredito.Data_Movimento, CessioneCredito.Note_Liq_Cessione_Credito, CessioneCredito.Importo, " & _
" Agenzie.Denominazione, DCount("[Anno]","CessioneCredito","[Anno]=" & [Anno]) AS GroupSize, " & _
" 1 + DCount("[Anno]","CessioneCredito","[Anno]=" & [Anno] & " AND [Data_Movimento] > #" & [Data_Movimento] & "#") AS GroupPos, " & _
" DSum("[Importo]","CessioneCredito","[Anno]=" & [Anno]) AS GroupTotal " & _
" FROM CessioneCredito INNER JOIN Agenzie ON CessioneCredito.ID_Agente=Agenzie.ID_agenzia " & _
" WHERE(((CessioneCredito.ID_Agente) = [Reports]![R_StoricoCessCredAg]![ID_Agente]))" & _
" ORDER BY CessioneCredito.Data_Movimento DESC; "While saving it, it shows the code in RED COLOR due to compilation error. Something was wrong as I have no idea.. Any thought on this.
As I am writing the query in Me.Recordsource = "SQL query", where i should place the below listed code in the query as per Plog???
Change the Year Control Source to this:
=IIf([GroupPos]=1,[Year])
Change the GroupTotal Control Source to this:
=IIf([GroupPos]=[GroupSize],[GroupTotal])
If this is not possible in Me.recordsource, then its advisable to use recordset like writing in two different queries.
Code:
Dim ds1 As Recordset
Dim ds2 As Recordset
myquery1 = "SELECT CessioneCredito.ID_Agente, CessioneCredito.Data_Movimento, CessioneCredito.Importo, CessioneCredito.Anno, CessioneCredito.Note_Liq_Cessione_Credito, Agenzie.Denominazione
FROM CessioneCredito INNER JOIN Agenzie ON CessioneCredito.ID_Agente=Agenzie.ID_agenzia
WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))"
myquery2 = "SELECT Sum(CessioneCredito.Importo) AS SumOfImporto, CessioneCredito.Anno, Max(CessioneCredito.Data_Movimento) AS MaxOfData_Movimento, Min(CessioneCredito.Data_Movimento) AS MinOfData_Movimento
FROM CessioneCredito GROUP BY CessioneCredito.Anno, CessioneCredito.ID_Agente
HAVING (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))
ORDER BY CessioneCredito.Anno DESC , Max(CessioneCredito.Data_Movimento) DESC"
Set db = CurrentDb()
Set ds1 = db.OpenRecordset(myquery1)
Do Until ds.EOF
'
' *** where the validation parts comes here to move the year and ***.
' *** amount field for max and min of date field for every year ***
'
Ds.MoveNext
Loop