Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Exclamation Unanswered: End of the Month

    how would I make my dates calculate for the end of the month, some months have 28 days, 30, 31 days. This is the code i'm using and the field I'm basing it off of "=DateSerial(Year([FIRSTRENEWALDATE])+2,Month([FIRSTRENEWALDATE]),Day([FIRSTRENEWALDATE]))"

  2. #2
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by desireemm
    how would I make my dates calculate for the end of the month, some months have 28 days, 30, 31 days. This is the code i'm using and the field I'm basing it off of "=DateSerial(Year([FIRSTRENEWALDATE])+2,Month([FIRSTRENEWALDATE]),Day([FIRSTRENEWALDATE]))"
    Are you looking to retrieve all records where the date is the end of the month?

    Perhaps you could add two fields with one being TheDayay([FIRSTRENEWALDATE]) and the other being M:Month([FIRSTRENEWALDATE]) which will give you the day of the month and the month itself.

    You could then do an IIf along the lines of IIf(([M] Like 3 Or [M] Like 12) And [TheDay] Like 31, etc.

    The funny faced is popping in, must be the : before Day([FIRSTRENEWALDATE])
    Last edited by Mike375; 05-20-04 at 16:14.

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    Return the Last Day of the Month, the Beginning of a Quarter or End of a Quarter Based on a Specific Date

    Access' Date Add and DateDiff functions generally fill most needs for date manipulation. However, there are a few instances where you need to return a date for comparison purposes which are not simple additive date result from a current date. Two examples are specifying the date for the end of the current month (e.g. perhaps for billing purposes), or gathering data which occurs, or is scheduled to occur based on whether it is after the beginning of a quarter, or before the end of a quarter. The following two functions provide the code to determine the end of the month from a specific date and the first or last day of any yearly quarter based on a specific date. (The second function below, providing the quarterly dates relys upon the end of the month function so both must be included in your module.) Here's the end of the month function:
    Code:
    Function EOMonth (Anydate)
    '------------------------------------------------------------------------
    'Purpose:	Returns the last day of the month for the date specified
    'Accespts:  A Date or Date Variable.
    'Returns:	VarType Date 
    '------------------------------------------------------------------------
    On Error GoTo Err_EOM
    	Dim NextMonth, EndofMonth
    	NextMonth = DateAdd("m", 1, Anydate)
    	EndofMonth = NextMonth - DatePart("d", NextMonth)
    
    	EOMonth = EndofMonth
    
    Exit_EOM:
    	Exit Function
    Err_EOM:
    	MsgBox "Error" & " " & Err & " " & Error$
    	Resume Exit_EOM
    End Function
    Here's the Function to determine the beginning date or ending date of any quarter base on a date supplied:

    Code:
    Function BEQuarter (ByVal Anydate, BeginOrEnd As Boolean) As Variant
    '-------------------------------------------------------------------
    'Purpose:	 Returns the beginning or the end of a quarter
    'Uses:		 EOMonth() Function
    'Input:		AnyDate: A date value, use of #'s to signify a date when variable
    '				from a query or the immediate window.
    BeginOrEnd: 0 Finds Beginning of Quarter, -1 Finds End of Quarter
    ''Returns:	VarType Date
    '-------------------------------------------------------------------
    On Error GoTo Err_EOQ
      If BeginOrEnd <> 0 And BeginOrEnd <> -1 Then
    	 MsgBox "Error: BeginOrEnd must be 0 or -1"
    	 GoTo Exit_EOQ
      End If
    
      Dim EndofQuarter, BeginofQuarter, PrevQuarter
      Static MonthVar(12) As Integer
    
      If MonthVar(12) = 0 Then
    	MonthVar(1) = 2
    	MonthVar(2) = 1
    	MonthVar(3) = 3
    	MonthVar(4) = 2
    	MonthVar(5) = 1
    	MonthVar(6) = 3
    	MonthVar(7) = 2
    	MonthVar(8) = 1
    	MonthVar(9) = 3
    	MonthVar(10) = 2
    	MonthVar(11) = 1
    	MonthVar(12) = 3
      End If
    
    	Anydate = Anydate - DatePart("d", Anydate)
    	EndofQuarter = DateAdd("M", MonthVar(DatePart("M", Anydate)), Anydate)
    	EndofQuarter = EOMonth(EndofQuarter)
    	
    	If DatePart("m", EndofQuarter) = 6 Then
    		BeginofQuarter = DateAdd("q", -1, EndofQuarter) + 2
    	Else
    		BeginofQuarter = DateAdd("q", -1, EndofQuarter) + 1
    	End If
    	
    	If BeginOrEnd = -1 Then
    	   BEQuarter = EndofQuarter
    	Else
    	   BEQuarter = BeginofQuarter
    	End If
    
    Exit_EOQ:
    	Exit Function
    Err_EOQ:
    	MsgBox "Error" & " " & Err & " " & Error$
    	Resume Exit_EOQ
    End Function
    Hope this helps........


Posting Permissions

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