I need the good way make query report in 3, 6, and 12months (1 Viewer)

[Sokkheng]

Could you please guide me the good way for make query report by month (3months, 6months, 12months).
i need this report for show by month and by expense account.
thanks for any help.
Sokkheng

 

[Gasman]

Make one report and pass in the period you want and the title period as an OpenArgs.
I did pretty much the same for forms, but my date calculation was a little more complicated, so I used three dedicated queries.

Private Sub Form_Load()
' Add which period we are showing to form title label
If Not IsNull(Me.OpenArgs) Then
    Me.lblTitle.Caption = Me.lblTitle.Caption & Me.OpenArgs
    Me.RecordSource = "qry" & Me.OpenArgs
End If
DoCmd.GoToRecord acDataForm, Me.Name, acLast
End Sub

In the form that has the buttons

Private Sub cmdMonthly_Click()
    DoCmd.OpenForm "frmPeriod", , , , , acDialog, "Monthly"
End Sub

Private Sub cmdWeekly_Click()
    DoCmd.OpenForm "frmPeriod", , , , , acDialog, "Weekly"
End Sub

Private Sub cmdDaily_Click()
    TempVars("WeekNum").Value = DatePart("ww", Me.cboDates.Column(1))
    DoCmd.OpenForm "frmDailyList", acFormDS, , , , acDialog
End Sub

I still used the one form though as the output.
You could use the WHERE parameter for the date criteria.

 

[Ranman256]

make a form, say frmRpts. On it have 2 text boxes for dates: txtStartDate, txtEndDate.

The user selects a begin and end dates
put some buttons to auto fill in the date boxes for quick date ranges: weekly, monthly.

The base query Q1, that reads the boxs for criteria...
select * from table where [DateEvent] between forms!frmRpts!txtSTartDate and forms!frmRpts!txtEndDate

only 1 report (or query) needed for any date range.

 

[Ranman256]

 

[Sokkheng]

Thanks for your reply, for my idea i need to make query for send to process report that in column for the months (Jan, Feb, Mar....) row for show the expense account.
i need to make 3 report from the query:
1- Report 3 months (Jan, Feb, Mar)
2-Report 6 months (Jan, Feb, Mar, Apr, May, June)
3-Report 12month (Jan, Feb, ......... Dec)

 

[MajP]

Without knowing your data and what this data looks like, I am guessing you need to build a cross tab query. You may also have to build dynamic columns if the start and end month change.

 

[ebs17]

3-Report 12month (Jan, Feb, ......... Dec)

For 2023 or 2024?
In our country, the months repeat themselves every year - before you program, you need clarity in the task.

 

[Ranman256]