Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2011
    Posts
    6

    Unanswered: displaying the calendar months in access

    How would I set the sequence of months to display correctly in a report. I prepared a report for 2011 and wanted to show information for each month but the months are shown in alphabetical order, something I do not want. I want to have them in order of the calendar year (i.e., January, February March, etc). Please advise.

    Any assistance is greatly appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    set the sort order
    thats assuming you are stroing date values as date and not text.

    failing that you could do a classic kludge
    order by cdate(MyMonthText & "/01/" & year(now())
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2011
    Posts
    6

    displaying the calendar months in access

    Thanks healdem, but it is still not working. I am in Microsoft Access 2010.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is the datatype of your columns
    how have you designed the tables
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2011
    Posts
    6

    displaying the calender months in access

    The main table is set up with columns. One column is for the Payment Issued which set at the date/time format. I have done a query for 2011 from that table and the query sorts it alphabetically. I tried using the expression builder and inputted your suggestion, but it states there is an error. Thanks for your help.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK to diagnose what is going wrong here is going to need a lot more information


    how do you derive the month names
    where do you specify the sort order?

    the table design would help as would the query/queries you are using

    and /or a copy of the db (strip out anything confidential)
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2011
    Posts
    6

    displaying the calendar months

    Thanks Healdem for getting back to me. I will attach the database. See what you think. I really appreciate the help you are giving me. There is so much to learn with this, and I am learning new things all the time. Best Regards. database.zip

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok your problem is that you are grouping by month and year but showing the column in the report as string / text

    I;'d suggest changing the query by specifying an appropriate group by clause
    your essential problme is that you did formatting at query level when usually its best to format at user interface level (ie the report).

    your query Permit Fees Previous Month
    Code:
    SELECT DISTINCTROW Format$([Building Permit Fees].[Paymentdate],'mmmm yyyy') AS [Paymentdate By Month], [Building Permit Fees].PermitType, Sum([Building Permit Fees].Amount) AS [Sum Of Amount]
    FROM [Building Permit Fees]
    GROUP BY Format$([Building Permit Fees].[Paymentdate],'mmmm yyyy'), [Building Permit Fees].PermitType, Year([Building Permit Fees].[Paymentdate])*12+DatePart('m',[Building Permit Fees].[Paymentdate])-1
    HAVING ((([Building Permit Fees].PermitType)='building'));
    changing it to
    Code:
    SELECT year([Paymentdate]) as pmtYr, month ([Paymentdate]) as pmtMth, [Building Permit Fees].PermitType, Sum([Building Permit Fees].Amount) AS [Sum Of Amount]
    FROM [Building Permit Fees]
    GROUP BY year([Paymentdate]), month ([Paymentdate]), PermitType
    HAVING [Building Permit Fees].PermitType ='building';
    and then 'conjuring' up the month can be done by using wither a function or some date/time function trickery

    function is
    public function GetMonth(MonthID as integer) as string
    GetMonth = "Ho bleeding Ho you supplied an invalid month of:" & MonthID
    select case MonthID
    case =1 GetMonth = "January"
    case = 2 GetMonth = "February"
    ...
    case =12 GetMonth= "Decemeber"
    end select
    end function

    is the root of the problem
    you don't need a distinct row with a group by clause

    therer seems to be some confusion about when to use spaces or not in your table / column names. whether you use a camel case or uderscore to denote words is up to you. camelcase: MyColumnName, Underscore: my_table_name. personally I prefer camelcase but there's good reasons to use all lowercase names as some db's don't handle uppercase names to well. more of a problem if you are working in a mized environment where there are *NIX and windows boxes

    I think you need to do some rethinking on your report handling
    ferinstance your code to get the previous month is fine for now, but if somone decides they want a reportfor Novemeber in 4 days time you are stuffed.. you have to make changes to the underlying query.
    similarly someone may request a yearly report for 2011 in 2012, your current code doesn't handle this

    what you can do is put a form in front of these reports allowing the user to specify the year, the month + year, the permit type and so on and get the report to use these as parameters when it opnes by changing the macro .openreport's openargs properties
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Dec 2011
    Posts
    6
    Thanks very much Healdem for the information. I will make the changes. But I need to sort the month in a report in sequence so it does not appear in alphabetical order. Please find attached screenshot. Click image for larger version. 

Name:	screenshot of report.jpg 
Views:	5 
Size:	173.9 KB 
ID:	12568

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the zip file attached to post #8
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Dec 2011
    Posts
    6

    many thanks

    Dear Healdem, you're awesome!!! Thanks so much for your help. I am going to look more into the coding end of it, which I do not know a lot about.

    Thanks again. Have a great day. Velma

Posting Permissions

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