Results 1 to 5 of 5
  1. #1
    wasim_sono is offline Advanced Beginner
    Windows XP Access 2013 64bit
    Join Date
    May 2005
    Location
    Pakistan
    Posts
    73

    Total no of days in a month

    Dear All



    Can any one tell me what the date function be use to calculate the no of days in a month.

    For example we a have date field which may have the values of 09-06-2006 and 24-10-2006. The function should calculate for first value which having month of June so the result be 30 and the second having October so result be 31.

    Thanks in advance.

    Wasim

  2. #2
    Join Date
    Apr 2006
    Location
    Manchester UK
    Posts
    11

    Total No of Days in a Month

    How about

    IIf(Month([YourDate])=2 And Year([YourDate]) Mod 4=0,29,Choose(Month([YourDate]),31,28,31,30,31,30,31,31,30,31,30,31))

    Best of Luck

    Ian

  3. #3
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Ian,

    There is a problem in your formula: not all years evenly divisible by four are leap years. For example, while 2000 was a leap year, 1900 was not and 2100 will not be. (Years ending in "00" must be evenly divisible by 400.)

  4. #4
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    To get the number of days in a month, from the date:

    =DateSerial(Year([DateField]), Month([DateField]) + 1, 1) - DateSerial(Year([DateField]), Month([DateField]), 1)

  5. #5
    Join Date
    Apr 2006
    Location
    Manchester UK
    Posts
    11
    Hi Patrick

    Of course you're right. I assumed for simplicity that most people would not want to deal with dates before 1901 or after 2099.

    Ian

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

Similar Threads

  1. total in textbox
    By micfly in forum Access
    Replies: 3
    Last Post: 11-09-2008, 11:24 AM
  2. SQL Query by day to end of month
    By tcasey in forum Queries
    Replies: 0
    Last Post: 10-07-2008, 09:55 PM
  3. Total records printed
    By LesleyA in forum Queries
    Replies: 3
    Last Post: 08-04-2008, 03:53 AM
  4. Default Value in table = Field plus 3 days
    By AmyLynnHill in forum Access
    Replies: 1
    Last Post: 08-03-2008, 01:58 AM
  5. "Previous Month" button
    By allochthonous in forum Programming
    Replies: 3
    Last Post: 09-10-2006, 12:15 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