Results 1 to 11 of 11
  1. #1
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147

    Convert to MMM/YYYY

    I want to convert dd/mmm/yyyy to MMM/YYYYY in my query. The audit date format in Table is 99/99/0000;0;_
    I created; Inspection Due: [Audit Date]+364 in the query. I want to show the next inspection is due in 1-year but only show the Month and Year.


    I tried to have the table show the change but can't get it to work there either.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Use the Format() function.

  3. #3
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    Where would I use that function and how?
    In Query I entered Format("MMM/YYYY") and it returned no records.
    Inspection Due: [Audit Date]+364

  4. #4
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    I figured it out by using the foramt you suggested in this way.
    Inspection Due: Format([Due Next],"mmm/yyyy")
    Now I want to show the inspection overdue by one month.
    I tried; Past Due: Format([Inspection Due],"mmm/yyyy")+1 but I couldn't get it to work. I also want it to show MMM/YYYY as well.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    For that you will want to use the DateAdd() function.
    Past Due: Format(DateAdd("m",1,[Due Next]),"mmm/yyyy")

  6. #6
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    Works Great! Thanks!

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    That is great but what happened? This thread was marked Solved at one point and now it is not????

  8. #8
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    You showed me a better solution.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    So it is still Solved and you could mark it so, right? It is a toggle so maybe you used the Thread Tool twice.

  10. #10
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    Yes, the problem is solved. Thanks!

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Thanks for marking it Solved again Brian and you are very welcome.

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

Similar Threads

  1. Number convert to date
    By Argus in forum Programming
    Replies: 1
    Last Post: 08-05-2009, 02:21 PM
  2. Convert query sql server for Access
    By webtony in forum Queries
    Replies: 0
    Last Post: 06-23-2009, 02:46 AM
  3. Replies: 1
    Last Post: 06-11-2009, 05:40 PM
  4. Replies: 0
    Last Post: 12-28-2008, 01:56 PM
  5. Convert string to date
    By ~SwAmPdOnKeY~ in forum Queries
    Replies: 4
    Last Post: 09-11-2008, 07:19 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