Results 1 to 7 of 7
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    month year date

    All. I have a ms access 2003 database that has a month/year table for 2012 i.e. january 2012, february 2012 that is used on a form as drop down. i want to add a month year table that has future years. The user would run reports based on the month and year. I will also be archieving data every 90 days and every year. Is it best to set up the tbl with all the months and a separate tbl with the year and concatenate the month and year in the field the uses need?


    I need something managable because eventually they need to select january 2013. By the way; I inherited this database as is.
    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,009
    Two tables probably faster to setup than the single table but either will work.

    By archive, do you mean moving records to another table or just setting a field in table?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Achieving data to another table so that the current table won't be so large. I am migrating this to SQL Server 2005 and I think i can just put this as a date field and format it mmmm/yyyy in the report so it will show month year(January 2012). This will eliminate the need for a separate date table; am I correct??

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,009
    If you want to only offer users dates for existing records, yes that is way to do it. I had thought you wanted to offer users a list of future month/year dates for selection to enter into a field.

    You will have to do the search on a query that has a field constructed with expression to format the date as month/year and then apply the month/year criteria selected by user to the constructed field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    You mean so that the dates will show up in the report as January 2012 or February 2013 .....? I can't just say mmmm/yyyy in the query?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,009
    Whether or not you want to display the month/year on report is secondary to the sorting & filtering you want to do. If you want to sort & filter by month/year, need a field that month/year criteria can by applied to. If the date in table is a full mm/dd/yyyy (with or without time part), need to calculate a field in query to extract month/year.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Got it! Thanks so much

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

Similar Threads

  1. Replies: 4
    Last Post: 01-10-2012, 06:26 PM
  2. Separating a date into day, month and year
    By teirrah1995 in forum Reports
    Replies: 3
    Last Post: 07-17-2011, 02:17 PM
  3. Current Month and Year-To-Date
    By DSnipeFunk in forum Access
    Replies: 4
    Last Post: 05-31-2011, 11:38 AM
  4. Replies: 2
    Last Post: 03-26-2011, 02:43 PM
  5. by year by month
    By nkuebelbeck in forum Reports
    Replies: 21
    Last Post: 03-24-2010, 01:53 PM

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 - Senior Forums