getting number to display with 8 digits

[BJS]

I have a result of a calculation that displays in a text box on my form.

I have dimensioned my numbers as double.

The result of the calculation could look like this:

Sample Answer 1 = 662.102106929135
Sample Answer 2 = 66210210.234

I want all my answers to display with 8 digits (excluding decimals or +- sign)

Sample Answer 1 should display as 662.10210
Sample Answer 2 should display as 66210210

Any idea how I can accomplish this?

I need to keep my numbers dimensioned as double; otherwise I run into a whole other issue.

I have an idea, but I'm not sure how to code it. My idea goes something like this:

num1 = 66210210.234

x = Len(num1)
x1 = total number of decimals and +- signs in the number

x2 = x - x1 (substract the number of decimals and +- signs from the original length of the number

If x2 > 8 then Left(x2, 8)

If this will work, could someone help me to code it?
I'd really appreciate some help with this. THANKS! BJS

 

[Mile-O]

Copy and past the code below into a module.

In a query build the new field by using the function

i.e. NewField: BuildFormat([MyField])

Public Function BuildFormat(ByVal dbl As Double) As String
    
    On Error GoTo Err_BuildFormat
    
    Dim strFormat As String
    Dim intLength As Integer
    Dim intCounter As Integer
    
    intLength = Len(CStr(CLng(dbl)))
    
    For intCounter = 1 To intLength
        strFormat = strFormat & "0"
    Next intCounter
    
    strFormat = strFormat & "."
    
    For intCounter = (intLength + 1) To 8
        strFormat = strFormat & "0"
    Next intCounter
    
    BuildFormat = Format(dbl, strFormat)
    
Exit_BuildFormat:
    strFormat = vbNullString
    Exit Function
    
Err_BuildFormat:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_BuildFormat
    
End Function

 

[BJS]

Mile-O-Phile,

YOU HAVE SAVED MY LIFE! THANK YOU SO MUCH!!!!

your code works like a charm!

I'm not sure I entirely understand what it is doing, but I have attempted to comment your code. Could you look at it and let me know if I have commented based on what it is actually doing?

HERE IS YOUR CODE, WITH COMMENTS:

Public Function BuildFormat(ByVal dbl As Double) As String

On Error GoTo Err_BuildFormat

Dim strFormat As String
Dim intLength As Integer
Dim intCounter As Integer

'determine the length of dblNum1, dblNum2 or dblNum3
intLength = Len(CStr(CLng(dbl)))

'create the format for the number, by starting with as many 0's
'as the length of the number
For intCounter = 1 To intLength
strFormat = strFormat & "0"
Next intCounter

'append a decimal after the 0's
strFormat = strFormat & "."

'after the decimal, add as many 0's as there are from the 8th digit
'to the decimal. This becomes the format of the number.
For intCounter = (intLength + 1) To 8
strFormat = strFormat & "0"
Next intCounter

BuildFormat = Format(dbl, strFormat)

Exit_BuildFormat:
strFormat = vbNullString
Exit Function

Err_BuildFormat:
MsgBox(Err.Description, vbExclamation, "Error #" & Err.Number)
Resume Exit_BuildFormat

End Function


THANKS AGAIN, MILE-0-PHILE!!!!!!!!

 

[Mile-O]

You are slightly off...I've commented it now.

Public Function BuildFormat(ByVal dbl As Double) As String
    
    On Error GoTo Err_BuildFormat
    
    ' variable declarations
    Dim strFormat As String ' to make the format string
    Dim intLength As Integer ' to get the count of numbers before the decimal point
    Dim intCounter As Integer ' loop counter
    
    ' get the count of numbers before the decimal point by first
    ' truncating the decimal part by converting the Double to a Long Integer
    ' and then converting the Long value to a String so that its length can
    ' be determined
    intLength = Len(CStr(CLng(dbl))) 
    
    ' loop for each number that's non decimal in order to build the part of 
    ' the Format string that's left of the decimal point
    For intCounter = 1 To intLength
        strFormat = strFormat & "0" ' the 0 makes it a required number unlike #
    Next intCounter
    
    ' insert the decimal point into the Format string
    strFormat = strFormat & "."
    
    ' build the remainder of the Format string for the characters to the
    ' right of the decimal point
    For intCounter = (intLength + 1) To 8
        strFormat = strFormat & "0"
    Next intCounter
    
    ' return the value of the function by changing the value of the Double
    ' passed to a String by using the Format string built within the function
    BuildFormat = Format(dbl, strFormat)
    
Exit_BuildFormat:
    strFormat = vbNullString
    Exit Function
    
Err_BuildFormat:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_BuildFormat
    
End Function

 

[BJS]

Thanks for the comments, Mile-O-Phile!
Now I understand!

You've been a GREAT help!!

BJS