Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Dates into Month's (2000 - SR2)

    I have a database for which the customer enters a date in the "05/12/2003" format. Now they want me to see if I can capture the amount of data by month and year. Is there a method of using the date format, where I can use the "05" to equal May, "06" equals June, and so on, and also capture the "2003" to represent data specifically for that year?

    So when reports are run for a calendar year it would show the data totals broken down by each month, and if a full report is run, it would show totals by calendar year. Any ideas? As always, thanks in advance for the assistance.

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

    Re: Dates into Month's (2000 - SR2)

    With the report open in design mode, select View | Sorting and Grouping. Enter the date field in Field/Expression, set Group Header or Group Footer (or both) to Yes, and select Year in Group On. In the second row, enter the date field too, and select Month in Group On. This will group your data by year and month.

    To display just the month in a text box, set its Control Source to the date field and its Format property to mmmm. This stands for the full name. Similarly,, to display only the year in another text box, set its Control Source to the date field too, but its Format property to yyyy.

    You can use expressions like =Sum([SomeField]) in the group header or footer to total data.

    You can also use the Report Wizard to design a report. It will offer options for sorting, grouping and totalling.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Dates into Month's (2000 - SR2)

    The problem I am have is not a report grouping issue, it is how to turn the entered date "5/12/2003" into the month of "May" and the year "2003". I need to re-code the date from the "5/12/200" in a query (I believe) so that I can figure out how many "May " dates I have (and every other month) and how many "2003" records I have (along with 2002 and 2001.

    Ulitmately I will be generating a nemerical report that details how many records for each Month and each year.

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

    Re: Dates into Month's (2000 - SR2)

    TheMonth:Format([DateField],"mmmm")
    TheYear:Year([DateField])

  5. #5
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Dates into Month's (2000 - SR2)

    Got it. Thanks.

Posting Permissions

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