Results 1 to 5 of 5
  1. #1
    Brian62 is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    147

    How to show all months


    I want to show all 12 months even though they are not present yet in the table. January, February.....

    Here is the script I am using:

    Month: Format([Date],"mmmm")

    It only shows the months that are in the table. I need to show all months even though the field is empty. Is there another way in the report to show each month but has no data to fill it instead and show 0 in the cell?

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    There are serveral ways to do this but the usual on is to for the column headings using the In() to populate the column heads foar a ll periods in the date range.

    Here is a link to possible alternaive solution

    http://www.access-programmers.co.uk/...d.php?t=174215

    David

  3. #3
    Brian62 is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    147
    I don't understand what you are trying to tell me. How would I applly the IN() to popluate all the months even if they are not in the date column.

    Where would I apply this in the query or is there another way to do this?

  4. #4
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Ok to do it manually, I am summsing that this is a crosstab query. Then go to the design of your query and click on the properties for the column heading field.

    You will see a property Column Headings, in there type in "Jan",Jeb","Mar", etc in this or whatever format the headings are being displayed. This will force a column for each period irrespective of there being data or not. Now save a retry. Then re desing the query and look at the SQL of the crosstab and you will see how it uses the In() command mentioned earlier.

    David

  5. #5
    Brian62 is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    147
    It worked! Thanks so much!!!!

    I put "January","February","March","April","May","June", "July","August","September","October","November"," December" in the Column Heading in the Column Headings section of Property Sheet as mentioned...
    Last edited by Brian62; 10-20-2009 at 09:20 AM. Reason: Correction

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Its Only A Poor Zero...Why wont it Show???
    By chompgator in forum Access
    Replies: 2
    Last Post: 03-08-2009, 01:54 PM
  2. Replies: 0
    Last Post: 02-27-2009, 01:39 PM
  3. Replies: 4
    Last Post: 10-29-2008, 11:53 AM
  4. Show some or all
    By protean_being in forum Queries
    Replies: 1
    Last Post: 05-28-2008, 05:33 PM
  5. A months query
    By Peljo in forum Access
    Replies: 1
    Last Post: 02-18-2008, 09:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums