GENERATE REPORTS IN MS ACCESS ACCORDING TO DATE RANGES

GENERATE  REPORTS IN MS ACCESS ACCORDING TO DATE RANGES

This blog post explains how to create ms access form to generate reports according to the given date rangers.Video tutorial for this lesson is available in You Tube.Video is linked to this blog,please see below;
First Step of this process is to create a MS Access database to test this.you can create simple database to do this.In my example I have created a database called "Data Range".

Then I created  a table called tblData.Please see below screen shot to understand table structure.

Please download sample database to see full table fields.

Once you create the table you have to create a Query based the tblData. There you have to choose column you want to display in your access report.

Follow this path to create a Query based on the table.

Select "Create" from the ribbon and then select "Query Design".

Design Query according to the above image.Then you have to create a Form to enter data.

Take two labels and name them as "Start Date" and "End Date".

You need two text boxes and name text boxes as follows;

Text Box 1 = txtdatefrom

Text Box 2 =txtdateto

Put Five Command Buttons on the form,and you have to name them as follows;

Button 01 

Caption  = "Today"

Button Name = cmdtoday

Button 02

Caption  = "Week"

Button Name = cmdweek

Button 03

Caption  = "Week"

Button Name = cmdweek

Button 04

Caption  = "Month"

Button Name = cmdmonth

Button 05

Caption  = "Year"

Button Name = cmdyear

Button 06

Caption  = "Report"

Button Name = cmdReport

Form Name = frm_DateRange

Based on the created Query generate report and keep its name as Query 1 for testing purpose.Report is as follows when it opens in Design View

Once you complete above steps you have small coding part in using VBA.Click on the Visual Basic Icon to access the code editor.Please view the below image for more details;

Once you accessed to the VBA code editor,you have to do coding part as follows;

Coding Part

Option Compare Database

Private Sub cmdtoday_Click()

'Sets the Date From and Date To text boxes

'to Today's Date

    Me!txtdatefrom = Date

    Me!txtDateTo = Date

End Sub

========================================================================

Private Sub cmdweek_Click()

'Sets the Date From and Date To text boxes

'to show complete working week (Mon - Fri)

    

    Dim today

    today = Weekday(Date)

    

    Me!txtdatefrom = DateAdd("d", (today * -1) + 2, Date)

    Me!txtDateTo = DateAdd("d", 6 - today, Date)

End Sub

========================================================================

Private Sub cmdmonth_Click()

'Sets the Date From and Date To text boxes

'to show complete month (from start to end of current month)

    Me!txtdatefrom = CDate("01/" & Month(Date) & "/" & Year(Date))

    Me!txtDateTo = DateAdd("d", -1, DateAdd("m", 1, Me!txtdatefrom))

End Sub

========================================================================

Private Sub cmdyear_Click()

'Sets the Date From and Date To text boxes

'to show complete current year

    Me!txtdatefrom = CDate("01/01/" & Year(Date))

    Me!txtDateTo = DateAdd("d", -1, DateAdd("yyyy", 1, Me!txtdatefrom))

    

End Sub

========================================================================

Private Sub cmdReport_Click()

    On Error GoTo Err_cmdReport_Click

    Dim stDocName As String

    stDocName = "Rpt_Date"

    

'Check values are entered into Date From and Date To text boxes

'if so run report or cancel request

    If Len(Me.txtdatefrom & vbNullString) = 0 Or Len(Me.txtDateTo & vbNullString) = 0 Then

        MsgBox "Please ensure that a report date range is entered into the form", _

               vbInformation, "Required Data..."

        Exit Sub

    Else

        DoCmd.OpenReport stDocName, acPreview

    End If

Exit_cmdReport_Click:

    Exit Sub

Err_cmdReport_Click:

    MsgBox Err.Description

    Resume Exit_cmdReport_Click

End Sub

After you have placed codes as above you have to set a criteria for "Business Date" in your query design.It can be shown as follows;

>=[Forms]![frm_DateRange]![txtDateFrom] And <=[Forms]![frm_DateRange]![txtDateTo] 

Once you have completed all the above steps you can test your programe.It is as follows;

Here I have given YEARLY Data to view Report.Then It will shoe you the yearly data in your access report "Query 1"

Report is for above criteria is as follows;

You can download sample file by clicking following link;

Download

For Video tutorial of this lesson please play following video.