Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2011
    Posts
    42

    Unanswered: getting the last day No of the month

    how to get the last day no of a month based on a date control ;

    example : textbox1 ="21/4/2011"
    : textbox1 ="15/6/2011"
    : textbox1 ="20/7/2011"
    how to get the last day ending of the month (30) in the first example and (30) in the second example and (31) in the last example

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Do you want just the day, i.e. 39 or 31, or do you want the entire date, ie 30/06/2011?

    This will return just the day:

    Format(DateSerial(Year(YourDate), Month(YourDate) + 1, 0), "dd")

    This will return the entire date:

    Format(DateSerial(Year(YourDate), Month(YourDate) + 1, 0), "dd/mm/yyyy")

    The Format() function has to be used because Access is American-centric and would return the date in the US format of

    mm/dd/yyyy

    not

    dd/mm/yyyy

    as you are using.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2011
    Posts
    42
    this is exactly what i need
    thanks

  4. #4
    Join Date
    Mar 2011
    Posts
    42
    can I use the same function to the first day of the month

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Code:
    FirstOfMonth = Format(DateSerial(Year(YourDate),Month(YourDate),1),"dd/mm/yyyy")
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Mar 2011
    Posts
    42
    response : argument not optional (dateserial)

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Can you copy and paste your exact code here? I just checked my code again and it gives the first of the month, as intended.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the sneakeist way of doing that is to find the 1st of the next month then subtract one day using the dateadd function
    of course you could write a function that returned the value based on the relevant logic and also account for leap years.
    or there's this aircode, not tested, not even typed in the Access IDE so it may not complie, may not work and so on, you may have to do some of the work yourself
    Code:
    Public Function getEndofMonth(thisDate) As Date
    If (Month(thisDate) = 12) Then ' the we need to add one to the year and set the date to 1st Jan
      getEndofMonth = CDate(Year(thisDate) + 1 & "/01/01")
    Else 'we add one to the current month
      getEndofMonth = CDate(Year(thisDate) & "/" & Month(thisDate) + 1 & "/01")
    End If
    getEndofMonth
    Last edited by healdem; 04-07-11 at 10:32.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Why in the world would you want to take that approach when a single line of code will do the same thing?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •