?
Solved

Defining Quarterly "dates" in a report

Posted on 2005-04-26
13
Medium Priority
?
355 Views
Last Modified: 2006-11-18
[A] I have a report that I normally prepare every three months by typing the following dates
      using "date range" - instead of typing these dates.

             Dates                              Quarter
  01/01/05 - 03/31/05                      Qtr 1    
  04/01/05 - 06/30/05                      Qtr 2
  07/01/05 - 09/31/05                      Qtr 3
  10/01/05 - 12/31/05                      Qtr 4

I 'll like to just pick the appropriate Quarter and print the report on a yearly basis. In other words in 2006, Qtr1 , Qtr 2, Qtr 3, Qtr 4 will simply be selected and the report printed.

[B]. The current date format for the date each report is printed is: =Format(Date(),"Short Date")
       I'll like to change this date to change such that new format would be each quarter plus 1
       for instance if printing quarter1, then date report is printed should be:

                   "Qtr 1 + 1 = 04/01/05
   If the date falls on a weekend or on a holiday (DlookUp tblholiday) and insert the next business date

0
Comment
Question by:billcute
  • 6
  • 5
  • 2
13 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 13873648
I'm a little confused,

I have lots of questions
:)

First you say: "I normally prepare every three months by typing the following dates"
Then you say: "instead of typing these dates"

1. So are you typing in the dates or not? (What do you type in, and where?)

2. What is "date range”?

3. How can you "pick the appropriate Quarter and print the report on a yearly basis"? (If it is December 27th, and you picked "qtr3", how do you get a Yearly Report?)

4. Is the report based on a query or a table?

5. How is the report structured (can you give us a list of all the: grouping options, sort order, summary sections)?


My guess is that you want to type in a "Quarter" somewhere and generate a report, for that quarter, in the current year.
If so, you need to base your report on a query. The query will be based on your table.

The SQL behind the query will look something like this (you will have to substitute your table and field names where appropriate):

SELECT tblDates.Date
FROM tblDates
WHERE (((DatePart("q",[Date]))=[Enter a Quarter]) AND ((Year([Date]))=Year(Date())));

So when you open your Report, it will ask you "Enter a Quarter", you would then type in: 1,2,3, or 4, and the report would generate. Showing only data from the selected Quarter, in the current year.

Now you could jazz this up with:
1. comboboxes that only list 1st, 2nd, 3rd, or 4th
2. A main form to select the quarter and a button to click to generate the report.

But let's jus see if I understand your question correctly
0
 
LVL 5

Expert Comment

by:BevinManian
ID: 13873682
Some light on your first question.

You can use the below query to generate identify which quarter a date belongs to and filter out your report based on what you might choose from a form.

SELECT tbl.DATE, DateDiff("q",CVDate("1/1" & Str([INPUT YEAR])),[DATE])+1 AS QUARTER
FROM tbl
WHERE (((DatePart("yyyy",[DATE]))=[INPUT YEAR]));

This will list all dates and corresponding quarters they belong to for any given year.  This SQL code gives u the efficiency of choosing any year - assuming your table has data for multiple years.  You may choose first the year and then the quarter from a form to do the filtering.  In that case [INPUT YEAR] will be replaced with something like
forms![formname]![yearfield]

The second question is still a bit unclear.  

I can suggest the dateadd() function to add dates and then using IIF() function to add move to the next business day.

HTH
Bev
0
 
LVL 4

Author Comment

by:billcute
ID: 13874482
boag2000,
Thanks for responding.

1. I first desgned the SQL for the report as described by "BevinManian" above an then specify a date range criteria
    in the SQL:
>=[forms]![Report Date Range]![Beginning Trans Date] And <=[Forms]![Report Date Range]![Ending Trans Date]

then I created a form called such as: have a form called "Report Date Range form" sshowing:
fields for "Begining Date" and the "Ending Date" with a "Preview" button on it. I will first type in the Begining date and the Ending date of the Quarter under review, then click the preview button which will then open my report for that range of date.

(2). For each year the dates are already pre-defined as specified in my original question - see below:
           Dates                              Quarter
  01/01/05 - 03/31/05                      Qtr 1    
  04/01/05 - 06/30/05                      Qtr 2
  07/01/05 - 09/31/05                      Qtr 3
  10/01/05 - 12/31/05                      Qtr 4

(3). Qtr 3 date for 2005 is from  07/01/05 - 09/31/05 and Qtr 3 for 2006 will be the same. The dates are constant

(4).  The report is based on a query

(5).  In order to answer this question, I will have to send you a sample of my current report. Just click on my username,
       you'll find my email and drop me a line.

Regards
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 4

Author Comment

by:billcute
ID: 13874497
BevinManian ,
You seems to understand my request easily.
In order to get a clearer understanding of my post, I can send you a sample as well if you drop me a line.
0
 
LVL 5

Expert Comment

by:BevinManian
ID: 13874518
Sure billcute,
send me a sample

Bev
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 13877815
billcute,

Sorry for the confusion, I'll leave it to you and BevinManian to work this out

take care
0
 
LVL 4

Author Comment

by:billcute
ID: 13886482
boag2000,
Thanks for trying.

Cheers
Bill
0
 
LVL 5

Expert Comment

by:BevinManian
ID: 13899879
Bill,
Prototype sent on Thursday!

The form’s changed a bit to include a specific year and the quarter for which the report needs to be printed.

Based on the above inputs, the Date Range will be automatically chosen and displayed on the form.  The user has no access to the Date Range fields.

On clicking the preview button, the report opens up.  The Subject Heading will dynamically pick up the YEAR, QUARTER and the DATE RANGE.

The Report Date on the form header will display the last day of the specific quarter + 1 Day.  If it falls on a Sunday, the display will jump to the next day.

Bev

0
 
LVL 4

Author Comment

by:billcute
ID: 13900289
Bev,
Thanks for a protype form. Now, I need help in designing codes that will make the form work.
What steps will you suggest that I take in order to achieve my desired objectives.

Regards,
Bill
0
 
LVL 5

Expert Comment

by:BevinManian
ID: 13900332
Bill,
I feel the form in the prototype fulfills your main objectives.

Let me know what other specific areas or issues that need to be addressed.

Bev
0
 
LVL 5

Expert Comment

by:BevinManian
ID: 13900372
Bill
I understand your point of concern.

The main objective of your database is to keep your user interface, ie, forms and reports, well designed and parameterized - thus giving a flexible and rigid means to drill data from your database.

By keeping open date ranges, the user can specify ANY dates or quarters he/she needs reports for.

Keep your data "normalized".  This means keeps multiple related tables rather than one huge mother of a table.  This way queries can be made more efficient.

Any more Access tips and tricks can be provided, if need be.

HTH
Bev
0
 
LVL 5

Accepted Solution

by:
BevinManian earned 2000 total points
ID: 13900407
Bill,

Alright.  I presume the below will help your goal of giving user the flexibility of choosing specific quarters for any year.....


 
(1). Create two "Drop Down" combos for YEAR and QUARTER INPUTS on your form
      which will utilize your main table. The list must be dynamic so you can choose   any year you want provided there is a valid entry in the table and ensure that QUARTER drop
     down is hard coded with values 1 to 4.

(2). In order to fill in the Date Ranges, you should design your queries that will use these date
      ranges to produce the recordset required in your report..
 
(3). Add a command button and name it  “Preview” and place this code in its OnClick properties:
 
     Dim mth As String
     Dim dy As String
   
If IsNull(Me.[input year]) Or IsNull(Me.QTR) Then
    MsgBox "The YEAR & QUARTER must be filled in!"
    Exit Sub
Else
     Select Case Me.QTR
        Case 1
            mth = "1"
            dy = "31"
        Case 2
            mth = "4"
            dy = "30"
        Case 3
            mth = "7"
            dy = "30"
        Case 4
            mth = "10"
            dy = "31"
    End Select
   
    Me.BeginDate = CVDate("1/" & mth & "/" & Str([input year]))
    Me.EndDate = CVDate(dy & Str(Val(mth) + 2) & "/" & Str([input year]))
   
    DoCmd.OpenReport "QReport-For_1stQ_2005", acViewPreview
   
End If
 

5. Remember to design your report headings in a dynamic way so that it gets
   its' values from the form itself. This implies that the form needs to be open for the
   report to get the date range, Quarter & Year values in the heading.

6. For the report header date, which has the Day+1, you may use a "looping code"
    to take care of Saturdays and Sundays.  
 
     Dim DT As Date
     Dim ctr As Integer
    ctr = 1
    Do While True
    DT = DateAdd("d", ctr, Forms![report date range]![EndDate])
   
    'if a Weekned add another day
    If DatePart("w", DT) = 1 Or DatePart("w", DT) = 7 Then
        ctr = ctr + 1
    Else
        Exit Do
    End If
Loop
Me.DatePrinted = DT


----

1.  For the report footer date, make the control source as
=[dateprinted]
2. Weekdays for [date]+1 is taken care of in the above code


HTH
Bev.
0
 
LVL 4

Author Comment

by:billcute
ID: 13900414
Bev,
I followed your steps above and added your suggested codes above it works great.

Thanks
Bill
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question