Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Burbank, Illinois, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Date Parameter (Access 2003)

    Hello

    I have a report named "Timesheet" that has two subreports within the Timesheet report. One of the reports, "Timesheet Subreport" contains the actual date, rate, hours and description of the job. The other report, "Timesheet Expense Subreport" contains all expenses for this employee. I had a date parameter on the report "Timesheet" that the user would enter the date, for example from date, 01/01/2005 to ending date 02/01/2005. This works fine, except I get all records that have the date after 02/01/2005. Can someone tell me why? Also, when I enter information into the Timesheet form, the information shows under that particular Client form 4 times. Why is this happening?

    Thank you for the help!
    Wendy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Date Parameter (Access 2003)

    What is the criteria line that filters the data according to the date parameter(s)?

    Your second question seems to be about a form instead of a report. What is the record source of the form or forms involved?

    It would help if you could post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Burbank, Illinois, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Date Parameter (Access 2003)

    Hi Hans

    I am attaching a copy of the database and all tables, queries, forms and reports. I have to say that I am stumped on what I did wrong. I had to convert the file to Access 97 in order to get the file size under 100k. I am using Access 2003 when doing the database.

    Thanks for the help!
    Wendy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Date Parameter (Access 2003)

    I don't see any date parameters for the report or subreports.
    And I don't see repetition of data either.
    Can you give an example, and/or provide more information? TIA.

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Burbank, Illinois, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Date Parameter (Access 2003)

    Hans

    Sorry about that. I took out the parameters when I changed some information and forgot to put them back in. When you enter the beginning date and ending date for the timesheet, you get all the information, not just want you wanted. I think I have another problem. On the Clients form, I click on Classes and enter class information for this client. I then click on Payments, and get the message to enter a class for this client. I did enter the class information for this client. Why is this happening? I am confusing myself.

    Thanks
    Wendy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Date Parameter (Access 2003)

    About the report:

    - Open the main report in design view.
    - Activate the Data tab of the Properties window.
    - Click in the Record Source property.
    - Click the ... button on the right of this property.
    - Change the Total setting for the DateWorked column from Group By to Where.
    - Close and save the record source, then close and save the report.
    The Group By caused the report to repeat the data for each DateWorked.

    You will have to enter the date criteria in the record source of the Time Sheet Subreport too. To avoid being prompted for the parameters twice, create a form with text boxes in which the user can enter the beginning and ending dates, and refer to those in the criteria for the main report and subreport:

    Between [Forms]![frmParameters]![txtStart] And [Forms]![frmParameters]![txtEnd]

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Date Parameter (Access 2003)

    The record source of the Clients Subform of the Clients form includes a query based on payments. Hence, if there are no payments, the subform is empty even if there are classes. It's probably not a good idea to combine payments and classes in one subform.

  8. #8
    Star Lounger
    Join Date
    Nov 2001
    Location
    Burbank, Illinois, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Date Parameter (Access 2003)

    Hans

    Thank you very much, I will try this and let you know what happens. My boss just threw another curve at me. Say, for instance, a class meets every Tuesday for 1 1/2 hours. He would like to be able to enter an employee worked at that particular class and have the timesheet automatically calculate the time for every Tuesday. For example, there are 5 Tuesdays this month. He would like to enter the class under the employee only once and have the timesheet calculate how many hours the employee worked. He would like this to automatically change every month by how many times that day falls in the month. 5 Tuesdays in March, 4 in April. I guess my question is, is this hard to do?

    Do you know what I mean?

    Thanks
    Wendy

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Date Parameter (Access 2003)

    Perhaps you can use the following custom function (the second function is an auxiliary function for the first one, but it can be used by itself too):

    ' NumberOfDaysInMonth will return the number of aDays in a given year and month.
    ' aDay can be 1=Sunday ... 7=Saturday

    ' Example:
    ' NumberOfDaysInMonth(2002, 11, 7) will return the number of Saturdays in November 2002.

    Function NumberOfDaysInMonth(aYear As Long, aMonth As Long, aDay As Long) As Long
    NumberOfDaysInMonth = (DayInMonth(aYear, aMonth + 1, aDay, 1) - _
    DayInMonth(aYear, aMonth, aDay, 1)) / 7
    End Function

    ' -----------------------------------

    ' DayInMonth will return the first, second, third, fourth, fifth or last day of type aDay
    ' in the given year and month.

    ' aYear is the year
    ' aMonth is the month
    ' aDay can be 1=Sunday ... 7=Saturday
    ' aNum can be 1, 2, 3, 4, 5 or 9=Last

    ' Could be improved by adding some validity checks

    ' Examples:
    ' DayInMonth(2002, 5, 6, 3) will return the 3rd Friday in May, 2002
    ' DayInMonth(2002, 12, 3, 9) will return the last Tuesday in December, 2002

    Function DayInMonth(aYear As Long, aMonth As Long, aDay As Long, aNum As Long) As Date
    If aNum = 9 Then
    DayInMonth = DayInMonth(aYear, aMonth + 1, aDay, 1) - 7
    Else
    DayInMonth = DateSerial(aYear, aMonth, _
    7 * aNum + 1 - WeekDay(DateSerial(aYear, aMonth, 1), aDay Mod 7 + 1))
    End If
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •