I've got a bunch of bond rating weights from 0 to 7 in increments
of .25.

My input file has decimal numbers from 0 to 7, but not in
increments of .25. e.g. 1.3, 2.1, and so-forth.

I need to convert those numbers to the next highest number in the
..25 increment scheme.

The only thing that comes to mind is a monster IIF() in the
spirit of:


Weight_Final_Omnibus:
IIf([weight_raw_omnibus]>7,999,IIf([weight_raw_omnibus] Between
6.7500001 And 7,7,IIf([weight_raw_omnibus] Between 6.500001 And
6.75,6.75,???)))


But I have a feeling that when I nest that IIF statement out to
handle all 29-or-whatever ranges I'm going to provoke the dreaded
"Expression too complex".

But even if that does not happen, the resulting expression (which
I'm plugging away on right now...) is going to be ugly - to read,
to debug, and to modify.

Can anybody suggest a cleaner and more scalable alternative?

Maybe some SQL or VBA syntax where it rounds a value up to the
next .25 increment?
--
PeteCresswell

Define a function to do the rounding.

'--------------------
' Round to nearest .5
'
' dValueToRound number to be rounded only accepts numbers > 0
'
Public Function Round5(ByVal dValueToRound As Double) As Double

Dim dFraction As Double ' fractional part of dValueToRound

Round5 = dValueToRound
If dValueToRound <= 0 Then
Exit Function
End If

dFraction = dValueToRound - Fix(dValueToRound)

If dFraction < 0.25 Then ' Round down to 2 dec
dFraction = Format(dFraction, "0.00")
Else
If dFraction < 0.7 Then ' Round down to .5
dFraction = 0.5
Else
dFraction = 1 ' Round up to 1
End If
End If

Round5 = Fix(dValueToRound) + dFraction

End Function

"(PeteCresswell)" <x@y.Invalid> wrote

Off the cuff thinking before Ron's offering came through (air code):

Public function Round25(ByValue vGivenValue as Variant) as Currency
Dim cIntermediate As Currency
cIntermediate = (vGivenValue * 4) + 0.999996
Round25 = Fix( cIntermediate ) / 4
End Function

May give you something to tinker with.


"paii, Ron" <none (AT) no (DOT) com> wrote



--
Clif McIrvin

Change nomail.afraid.org to gmail.com to reply by email.
(nomail.afraid.org has been set up specifically for
use in usenet. Feel free to use it yourself.)

Per paii, Ron:
Thanks.

I'm going to go that route until somebody comes up with some SQL
syntax.

Create a work table.

Iterate through it....

Or maybe even try embedding the call do Round5 into a query.
--
PeteCresswell

Per (PeteCresswell):
Forget the SQL syntax. It runs plenty fast embedded in a query
- with no prospects of greatly-increased data volume.
--
PeteCresswell

On 08/10/2010 15:43:12, "(PeteCresswell)" wrote:
Don't know if this helps

Function RoundIt(NoIn As Variant, Nearest As Single, UpDown As Boolean) As
Variant

Dim CurNoIn As Currency
Dim Quotient As Currency
Dim CurNearest As Currency
Dim Result As Currency
Dim Remainder As Currency
Dim DP As Integer

If InStr(Nearest, ".") = 0 Then
DP = 0
Else
' Access error in that len(.012) is reported as 4 although
' Nearest value is shown as 0.012
DP = Len(Right(Nearest, Len(CStr(Nearest)) - InStr(Nearest, ".")))
End If

CurNoIn = CCur(NoIn)
CurNearest = CCur(Nearest)
Quotient = CurNoIn / CurNearest
Result = Int(Quotient) * Nearest
Remainder = Round(Quotient - Int(Quotient), 6)
If UpDown = True Then
If Remainder > 0 Then
Result = Result + CurNearest
End If
End If

RoundIt = Round(Result, DP)

End Function

Phil

Pardon me, but here is a simple expression that should work for you.
-Int(-[weight_raw_omnibus] * 4)/4

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

That is so much better then my function

"John Spencer" <JSPENCER (AT) Hilltop (DOT) umbc> wrote

Per John Spencer:
Lovely.... !
--
PeteCresswell