Easy Question - Calculate Days in Month

[kerry2002]

I am sure that this is a real easy question for some...but I just can't seem to figure it out. I have designed a database which tracks loan information. At the end of the month we want to print out a statement to mail out. On this statement we need the interest calcualted based on the number of days in the month. Is there a way to program Access to ask which month/year we want to run this query on...and then automatically calculate the number of days in the month?

I do appreciate any response. I am kinda stuck at this point.

Thank you!!!

 

[antomack]

The following functions are from one of Microsoft's downloadable sample databases, search on microsoft.com for neatcd97.mdb

Function DaysInMonth(D As Variant) As Variant
'
' Returns the number of days in a month.
' Requires a date argument, since February can change if it's a leap year
'
  If VarType(D) <> 7 Then
    DaysInMonth = Null
  Else
    Select Case Month(D)
      Case 2
        If LeapYear(Year(D)) Then
          DaysInMonth = 29
        Else
          DaysInMonth = 28
        End If
      Case 4, 6, 9, 11
        DaysInMonth = 30
      Case 1, 3, 5, 7, 8, 10, 12
        DaysInMonth = 31
    End Select
  End If
End Function

Function DaysInMonth2(D As Variant) As Variant
'
' Returns the number of days in a month
' Requires a date argument, since February can change if it's a leap year
' Lets Access figure it out
'
  If VarType(D) <> 7 Then
    DaysInMonth2 = Null
  Else
    DaysInMonth2 = DateSerial(Year(D), Month(D) + 1, 1) - DateSerial(Year(D), Month(D), 1)
  End If
End Function

 

[raskew]

Try this. The system will figure out the Leap Year problem without user intervention.

Function LastDay(pMoYr As String) As Date
'*******************************************
'Name:      LastDay (Function)
'Purpose:   Returns last day of inputted month
'Inputs:    from debug window:
'           (1) ? lastday("02/2002")<enter>
'           (2) ? LastDay("02/2000")<enter>
'Output:    (1) 2/28/02
'           (2) 2/29/00
'Note:      With dates > 12/2000, pMoYr
'           must be entered in the mm/yyyy
'           format to avoid confusing the
'           system.  Dates < 01/2001 may
'           be entered as mm/yy
'*******************************************

Dim dteMyDate As Variant

dteMyDate = DateValue(pMoYr)
LastDay = DateSerial(Year(dteMyDate), Month(dteMyDate) + 1, 0)
End Function

 

[Cosmos75]

Start and End date - breakdown amount by month

Does anyone know if this'll help me do this.

I have a table with these fields

Amount
StartDate
EndDate

Say one record is this

Amount: $122
StartDate: 06/01/2002
EndDate: 07/31/2002

I want a query (Not even sure if it's possible??) to show the break-down of that amount by month!!

i.e. June has 30 days and July has 31,
so the amount for June is
- $122 * (30/(30+31)) = 60
so the amount for July is
- $122 * (31/(30+31)) = 62

 

[Susan Owen]

Thank You So Much!

This last day of the month function is great! I'm replying to Raskew, above.) I really appreciate you putting this up. It is so short and simple and effective. I'm using it to set parameters for an sql query to open a report, as well as to automatically generate two dozen different dates for the finance department.

Btw, Access only recognizes date in the American format, so I'm having to reformat European formatted dates, using the DateTime.Month, etc., functions.

 

[akumar118]

Excellent..

A Very Very Happy prosperous,Peaceful and a wonderful New Year.

I am a newbie to Access and has just began to do develop some database prog. on PMIS (Perso.Mgt. Inf.System for my office) I wanted date of Retment to be last day of a given month. I was confused how to get it.However, Casually I logged on to this site accidentaly and ur reply to a member. The Lastday function really solved my problem. Thank u very much dear.

Now, another request.

What I need?

I am making a database which should give the Opening balance of Leave Account+Credit during Ist half of the year and Dtfrom and Dt to Availed Leave and then CBal. The format is as follows:

Ob Credit from To Leaveavailed ClosingBal
100 25 1-5-04 5-5-04 5 120

120
Next Ob should be 120

Likewise I have two period i.e. Ist half (Jan to Jun) and IInd half from (Jul to Dec)

Can u help me please.

akumar

I want that the next OB should reflect the ClosingBal in a query.

Try this. The system will figure out the Leap Year problem without user intervention.

Function LastDay(pMoYr As String) As Date
'*******************************************
'Name:      LastDay (Function)
'Purpose:   Returns last day of inputted month
'Inputs:    from debug window:
'           (1) ? lastday("02/2002")<enter>
'           (2) ? LastDay("02/2000")<enter>
'Output:    (1) 2/28/02
'           (2) 2/29/00
'Note:      With dates > 12/2000, pMoYr
'           must be entered in the mm/yyyy
'           format to avoid confusing the
'           system.  Dates < 01/2001 may
'           be entered as mm/yy
'*******************************************

Dim dteMyDate As Variant

dteMyDate = DateValue(pMoYr)
LastDay = DateSerial(Year(dteMyDate), Month(dteMyDate) + 1, 0)
End Function