iif Query

[Richard G]

I am trying to write an iif statement which will give me the average figure based on a monthly total - does anyone know how to work this out based on days of the month ? So for example Months 1,3,5,7,8,10,12 have 31days
the rest 30, except of course Feb with 28 (or 29) I need to tell the query to divide the monthly figure for say August by 31 but by 30 for September and by 28(or 29) for February ?

Regards

Richard.

 

[Brianwarnock]

I wouldn't use an IIF , I would write a function passing the month number, year( for Feb calc) and total. then use a Case statement to select code.

Brian

 

[raskew]

Hi -

Combining the DateValue(), Day() and DateSerial() functions:

x = datevalue("02/2008")
? Day(DateSerial(Year(x), Month(x) + 1, 0))
 29 

x = datevalue("02/2006")
? Day(DateSerial(Year(x), Month(x) + 1, 0))
 28

HTH - Bob

 

[Brianwarnock]

Hi Richard had a bit of time on my hands so knocked this up, it may not be exactly what you want or the best code but it works and will give you the idea, that's if you haven't already done it.

Brian

Function davge(fddate As Date, fdtotal As Double) As Double

fdmonth = Month(fddate)
fdyear = Year(fddate)

Select Case fdmonth
Case 2
If (fdyear Mod 4) = 0 Then
davge = fdtotal / 29
Else: davge = fdtotal / 28
End If
Case 1, 3, 5, 7, 8, 10, 12
davge = fdtotal / 31
Case 4, 6, 9, 11
davge = fdtotal / 30
End Select

End Function

 

[raskew]

Hi again -

Here's an example you might try:

Public Function fAvg(pmmyyyy As String, ptot As Double) As Double
Dim dteHold As Date

   dteHold = DateValue(pmmyyyy)
   fAvg = ptot / Day(DateSerial(year(dteHold), Month(dteHold) + 1, 0))

End Function

Note that millenium years must be evenly divisible by both 100 and 400 to be a leap year. Year 2000 was a leap year, 1900 was not. Example:

? fAvg("02/1900", 328)
11.7142857142857

? fAvg("02/2000", 328)
11.3103448275862

Bob

 

[Brianwarnock]

Hi

That's pretty bloody neat Bob.

I didn't think that programs I wrote in the 1970s would be in use at the turn of the millenium, I was wrong, but I'm pretty damn certain that things will have changed by 3000 , but your technique covers it anyway.

You can go off people
Just joking

Brian