Clean Up of IF..Endif, etc. 1

[Ascentient]

For those of you who viewed my Search String thread here is what I have done with it.

This Code gets the job done, but it looks ugly. The synopsis of this debauchery is replacing the pennies position with a 0 or 5 depending on what the pennies position holds. (Missouri Rounding as it was termed in my previous thread.)

More or less when a discount is applied that discounted price is rounded to the nearest nickel. The are some several naming conventions I have not corrected yet. (IF you notice at all.)

Any suggestions on making this look "clean"?

Dim curDiscountedPrice As Currency
Dim curDollarAmount, curChangeAmount As String

Me![Description] = Me![cboMaterial].Column(1)

' Calculating Sale Price based on Price Scale.
If Form_frmQuotedJob.fraPriceScale.Value = 2 Then 'standard retail price
Me!PricePerTon = cboMaterial.Column(2)
Else 'Contractors Pricing
'Determine the Discounted Price and then break them apart for testing below.
curDiscountedPrice = (cboMaterial.Column(2) - (Round((cboMaterial.Column(2) * 0.06), 2)))
curChangeAmount = Format(curDiscountedPrice - Int(curDiscountedPrice), "#.#0")
curDollarAmount = Val(Split(Trim(Str(curDiscountedPrice)), ".", -1)(0))

'TESTING FOR 1 & 2 to Round Down to Zero.
If Right(curChangeAmount, 1) >= 1 And Right(curChangeAmount, 1) <= 2 Then
curChangeAmount = Format(Val(Mid(Trim(Str(curChangeAmount)), 2, 1) & "0") / 100, "#.#0")
End If

'TESTING FOR 3 & 4 to Round Up to 5 and 6 & 7 Round Down to 5
If Right(curChangeAmount, 1) >= 3 And Right(curChangeAmount, 1) <= 4 Or Right(curChangeAmount, 1) >= 6 And Right(curChangeAmount, 1) <= 7 Then
l1 = Val(Mid(Trim(Str(curChangeAmount)), 2, 1))
curChangeAmount = Val(Mid(Trim(Str(curChangeAmount)), 2, 1) & "5") / 100
End If

'TESTING FOR 8 & 9 to Round UP to Zero (.38 rounds to .40)
If Right(curChangeAmount, 1) >= 8 And Right(curChangeAmount, 1) <= 9 Then
If Mid(Trim(Str(curChangeAmount)), 2, 1) = 0 Then
curChangeAmount = 0.1
ElseIf Mid(Trim(Str(curChangeAmount)), 2, 1) < 9 Then
'Change condition for < 10 to check for Zero. Then perform data otherwise do below.
curChangeAmount = Val(Mid(curChangeAmount, 2, 1) + 1 & "0") / 100 ' Checks for anything less than .95
Else
' Change Amount is $1.00 since change was originally .98 or .99
curChangeAmount = 1 ' Pennies
End If
End If

'Form_frmQuotedJob.txtDiscPrice = curDiscountedPrice
'Form_frmQuotedJob.txtRegPrice = cboMaterial.Column(2)
'Form_frmQuotedJob.txtChange = curChangeAmount
'Form_frmQuotedJob.txtDollar = Val(Left(Trim(Str(curDiscountedPrice)), Len(Trim(curDiscountedPrice)) - 3))

Me!PricePerTon = (curDollarAmount + (curChangeAmount))

End If

 

[Page410]

Using Select Cases may help to 'clean' things up. Also, don't use bangs(!) if you don't have to. Referring to the form properties using the period(.) or the index using parentheses and quotes (ME("ControlName")) is more efficient for Access.

Dim curDiscountedPrice As Currency
    Dim curDollarAmount As String
    Dim curChangeAmount As String
    
    Me.Description = Me.cboMaterial.Column(1)
    
    ' Calculating Sale Price based on Price Scale.
    Select Case Form_frmQuotedJob.fraPriceScale
        Case 2 'standard retail price
            Me.PricePerTon = cboMaterial.Column(2)
        Case Else 'Contractors Pricing
           'Determine the Discounted Price and then break them apart for testing below.
           curDiscountedPrice = (cboMaterial.Column(2) - (Round((cboMaterial.Column(2) * 0.06), 2)))
           curChangeAmount = Format(curDiscountedPrice - Int(curDiscountedPrice), "#.#0")
           curDollarAmount = Val(Split(Trim(str(curDiscountedPrice)), ".", -1)(0))
           
           Select Case right(curChangeAmount, 1)
             Case 1, 2
                 curChangeAmount = Format(Val(Mid(Trim(str(curChangeAmount)), 2, 1) & "0") / 100, "#.#0")
             Case 3, 4, 6, 7
                 l1 = Val(Mid(Trim(str(curChangeAmount)), 2, 1))
                 curChangeAmount = Val(Mid(Trim(str(curChangeAmount)), 2, 1) & "5") / 100
             Case 8, 9
                 If Mid(Trim(str(curChangeAmount)), 2, 1) = 0 Then
                    curChangeAmount = 0.1
                 ElseIf Mid(Trim(str(curChangeAmount)), 2, 1) < 9 Then
                    'Change condition for < 10 to check for Zero. Then perform data otherwise do below.
                    curChangeAmount = Val(Mid(curChangeAmount, 2, 1) + 1 & "0") / 100 ' Checks for anything less than .95
                 Else
                    ' Change Amount is $1.00 since change was originally .98 or .99
                    curChangeAmount = 1 ' Pennies
                 End If
           End Select
    End Select
    
    'Form_frmQuotedJob.txtDiscPrice = curDiscountedPrice
    'Form_frmQuotedJob.txtRegPrice = cboMaterial.Column(2)
    'Form_frmQuotedJob.txtChange = curChangeAmount
    'Form_frmQuotedJob.txtDollar = Val(Left(Trim(Str(curDiscountedPrice)), Len(Trim(curDiscountedPrice)) - 3))

 

[Ascentient]

I thought about using the Case Select, but not sure why I went against it. I am thinking the was something I could not account for using it. I will revisit this and make appropiate changes.

Thanks.

 

[crobg]

Here's a function to do the rounding. It seperates the pennies from the dimes (hundreds and tenths) and just adds everything back up. It's not pretty but does the trick.

Function RoundToNearestNickle(curAmount As Currency) As Currency

Dim allCents As Double, Dimes As Double, Cent As Double, strCent As String, Dollars As Long

allCents = curAmount - Int(curAmount)
Dimes = Val(Left(allCents, 3))
Cent = allCents - Dimes
strCent = Right(Cent, 1)
Dollars = curAmount - allCents

Select Case strCent

Case 1, 2
Cent = 0

Case 3, 4, 6, 7
Cent = 0.05

Case 8, 9
Cent = 0.1

End Select

allCents = Dimes + Cent

RoundToNearestNickle = Dollars + allCents

End Function

 

[PHV]

Why not simply this ?
RoundToNearestNickle = Round(20 * curAmount) / 20

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[crobg]

That's even cleaner. I have Access 97 and don't have the Round function. So you could use the Excel round function instead.

WorksheetFunction.Round(20 * curAmount,0) / 20

 

[crobg]

To round to the nearest denomination of coinage change the Function to use decimals instead of fractions:

Function RNN(curAmount As Currency, dblFactor As Double) As Currency

RNN = Round(curAmount / dblFactor) * dblFactor

End Function

curAmount = The amount you want to round ($1.01)
dblFactor = The decimal you want to round to.

For the nearest nickle: dblFactor = 0.05
For the nearest dime: dblFactor = 0.1
For the nearest quater: dblFactor = 0.25

 

[Ascentient]

Thanks Guys, I will try some of the "easier to setup" functions and give it try.

I will report back soon.

 

[Ascentient]

Well, just to let you know, I made some changes to this on my retrieval side of my program and it works beautifully.

Thank you all for your feed back.

Ascent