Results 1 to 5 of 5
  1. #1
    mike02 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    245

    Create month end date, from Month(Numberic) and Year(Numeric)

    Hey,



    I have a crosstab query which sets up the Year (yyyy) and Month(mm) and i need it to create the month end date. What i have done is create a regular query and am trying to find an expression i can use to put the dates together and make it month end. I have used the date Part formula before but never had to go in this order, any ideas? cheers,

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    If I have understood your requirement correctly, one way could perhaps be :
    Data Available : Month ; Year.
    Get the 1st Date of the Month & Year under consideration.
    Get the 1st Date of the next Month.
    Subtract a day to get the last Date of the Month & Year under consideration.

    Thanks

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,921
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  4. #4
    mike02 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    245
    I used the Date serial function:

    Dateserial([Years],[Months],1)-1

    Worked like a charm!

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,921
    This should work as well: DateSerial([Years],[Months]+1,0)
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

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

Similar Threads

  1. Month and Day only not Year to create query
    By ssalem in forum Queries
    Replies: 3
    Last Post: 02-28-2013, 02:37 PM
  2. Year to DATE by Month
    By lugnutmonkey in forum Queries
    Replies: 1
    Last Post: 01-18-2013, 05:37 PM
  3. month year date
    By slimjen in forum Database Design
    Replies: 6
    Last Post: 04-17-2012, 01:02 PM
  4. Separating a date into day, month and year
    By teirrah1995 in forum Reports
    Replies: 3
    Last Post: 07-17-2011, 02:17 PM
  5. Current Month and Year-To-Date
    By DSnipeFunk in forum Access
    Replies: 4
    Last Post: 05-31-2011, 11:38 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 - Senior Forums