yes i'm using that. it's a bunch of parsing that's for sure.
ok here it is. why dont you make a development copy of your form and table so you can play with it. i'm not going for simply FORMATTING here. so instead I'm making SPEC actually contain text that appears as the correct number you want.
STEPS:
1) i changed the type of SPEC to TEXT in the table otherwise no matter what i do, it cuts of final zeros. I hope this will be ok with you. I dont think it will affect anything that you are doing, i.e. simply printing SPEC or displaying it on the screen. If at any time you need to do calculations with it, just use csng(SPEC) to convert to a number again. anyhow, change SPEC to TEXT: do this in a copy of your table and make sure all the data already in there converts properly.
2) in the form after i did #1, i made the format of SPEC be GENERAL NUMBER, decimal places AUTO. so even tho it's text, it looks like a number.
3) ok--in the form design, for the field RESULT, in the AFTERUPDATE property, choose
. if necessary, you might want to do this also in Low Limit AFTERUPDATE in case that figure gets edited later.
4) paste this in between the SUB and END SUB lines:
Dim sngResult 'Numeric conversion of RESULT (Text to Single)
Dim strLowLimit 'Low Limit
Dim DecPointPlace 'Character place of decimal point in Low Limit
Dim NoOfDecPlaces 'Number of decimal places to go out
Dim FinalCalc 'Final Calculation
Dim DecPointPlaceFinal 'Character place of decimal point in FinalCalc
Dim NoOfDecPlacesFinal 'Number of decimal places FinalCalc has
Dim strFinalCalc 'FinalCalc in str format
'If RESULT cannot be converted to a number, then it is text so quit; otherwise, convert to Single and store it in sngResult
If IsNumeric(Me.Result) Then
sngResult = CSng(Me.Result)
Else
Exit Sub
End If
'If LowLimit cannot be converted to a number, then it is text so quit; otherwise store the numeric equiv in numLowLimit
If IsNumeric(Me.[Low Limit]) Then
strLowLimit = Me.[Low Limit]
Else
Exit Sub
End If
'find decimal point placement in Low Limit
If InStr([strLowLimit], "."
= 0 Then
DecPointPlace = Len([strLowLimit])
Else
DecPointPlace = InStr([strLowLimit], "."
End If
'find number of decimal places that LowLimit has: NoOfDecPlace = length of LowLimit - place where the dec point is
NoOfDecPlaces = Len([strLowLimit]) - [DecPointPlace]
'Final Calculation: Rounds sngResult taking into consideration the number of decimal places of Low Limit
'Rounding formula example (to round to two places): (INT((x+.005)*100))/100
FinalCalc = CDbl((Int(([sngResult] + (0.5 * (0.1 ^ [NoOfDecPlaces]))) * (10 ^ [NoOfDecPlaces]))) / (10 ^ [NoOfDecPlaces]))
'The rounding formula will cut off final zeros, so now we have to put them back in if necessary
' (i.e. need 30.00 but got 30)
'Convert FinalCalc to string to determine how many decimal places it has.
strFinalCalc = CStr(FinalCalc)
'Perform same routine as above (on Low Limit) to find out how many decimal places FinalCalc has
If InStr([strFinalCalc], "."
= 0 Then
DecPointPlaceFinal = Len(strFinalCalc)
Else
DecPointPlaceFinal = InStr([strFinalCalc], "."
End If
NoOfDecPlacesFinal = Len(strFinalCalc) - DecPointPlaceFinal
'If Final Calc & Low Limit have same number of decimal places, we're done
'Otherwise, add in a decmial point if needed
'Then add in final zero's as characters (text) using STRING function
If NoOfDecPlacesFinal = NoOfDecPlaces Then
Me.Spec = FinalCalc
Exit Sub 'done
Else
If NoOfDecPlacesFinal = 0 Then
strFinalCalc = strFinalCalc & "."
End If
strFinalCalc = strFinalCalc & String((NoOfDecPlaces - NoOfDecPlacesFinal), "0"
Me.Spec = strFinalCalc
End If
LET ME KNOW HOW IT GOES
g
)