eryan
asked on
Manipulation of Numbers.
I'm trying to set up an interface text file between 2 systems. I'm reading an SQL database with fields defined as
REAL, therefore values like 7.0, 12.5, 8.28, 41.999 etc with a maximum of 3 decimal places.
The target system needs these values put in the interface
file into a field 18 characters long with 15 characters for
the pre-decimal, 3 for the post-decimal, but NO decimal point. i.e,
7.0 becomes " 7000"
8.28 becomes " 8280"
41.999 becomes " 41999" etc.
Could anyone explain to me how best to use the various
conversion functions etc in VB5 to do this.
Thanks.
REAL, therefore values like 7.0, 12.5, 8.28, 41.999 etc with a maximum of 3 decimal places.
The target system needs these values put in the interface
file into a field 18 characters long with 15 characters for
the pre-decimal, 3 for the post-decimal, but NO decimal point. i.e,
7.0 becomes " 7000"
8.28 becomes " 8280"
41.999 becomes " 41999" etc.
Could anyone explain to me how best to use the various
conversion functions etc in VB5 to do this.
Thanks.
ASKER
Edited text of question
ASKER
Edited text of question
Private Function ConvertNumber(ByRef pdNum As Double) As Double
' this is without rounding
Dim sTempNum As String
Dim iDecPos As Integer
sTempNum = Str(pdNum)
iDecPos = InStr(sTempNum, ".")
If iDecPos > 1 Then
sTempNum = Mid(sTempNum, 1, iDecPos - 1) & Mid(sTempNum, iDecPos, 4)
ElseIf iDecPos = 1 Then
sTempNum = Mid(sTempNum, 1, 4)
End If
ConvertNumber = Val(sTempNum)
End Function
' this is without rounding
Dim sTempNum As String
Dim iDecPos As Integer
sTempNum = Str(pdNum)
iDecPos = InStr(sTempNum, ".")
If iDecPos > 1 Then
sTempNum = Mid(sTempNum, 1, iDecPos - 1) & Mid(sTempNum, iDecPos, 4)
ElseIf iDecPos = 1 Then
sTempNum = Mid(sTempNum, 1, 4)
End If
ConvertNumber = Val(sTempNum)
End Function
These options deal only with numbers rather than converting to string and will hopefully run a bit more quickly.
Private Function ConvertNumber(ByRef pdNum As Double) As Double
' this is without rounding
Dim lTempWhole As Long
lTempWhole = Fix(pdNum)
ConvertNumber = lTempWhole + ((Fix((pdNum - lTempWhole) * 1000) \ 1) * 0.001)
End Function
Private Function ConvertNumber(ByRef pdNum As Double) As Double
' this is with rounding
Dim lTempWhole As Long
lTempWhole = Fix(pdNum)
ConvertNumber = lTempWhole + (((pdNum - lTempWhole) * 1000 \ 1) * 0.001)
End Function
Private Function ConvertNumber(ByRef pdNum As Double) As Double
' this is without rounding
Dim lTempWhole As Long
lTempWhole = Fix(pdNum)
ConvertNumber = lTempWhole + ((Fix((pdNum - lTempWhole) * 1000) \ 1) * 0.001)
End Function
Private Function ConvertNumber(ByRef pdNum As Double) As Double
' this is with rounding
Dim lTempWhole As Long
lTempWhole = Fix(pdNum)
ConvertNumber = lTempWhole + (((pdNum - lTempWhole) * 1000 \ 1) * 0.001)
End Function
Sorry, I left the decimals in. This will fix it.....
without rounding....
Private Function ConvertNumber(ByRef pdNum As Double) As Double
' this is without rounding
Dim lTempWhole As Long
lTempWhole = Fix(pdNum)
ConvertNumber = (lTempWhole * 1000) + (Fix((pdNum - lTempWhole) * 1000) \ 1)
End Function
Without rounding....
Private Function ConvertNumber(ByRef pdNum As Double) As Double
' this is without rounding
Dim lTempWhole As Long
lTempWhole = Fix(pdNum)
ConvertNumber = (lTempWhole * 1000) + ((pdNum - lTempWhole) * 1000 \ 1)
End Function
Using string with no rounding.....
Private Function ConvertNumber(ByRef pdNum As Double) As Double
' this is without rounding
Dim sTempNum As String
Dim iDecPos As Integer
sTempNum = Str(pdNum)
iDecPos = InStr(sTempNum, ".")
If iDecPos > 1 Then
sTempNum = Mid(sTempNum, 1, iDecPos - 1) & Mid(sTempNum, iDecPos + 1, 3)
ElseIf iDecPos = 1 Then
sTempNum = Mid(sTempNum, 2, 3)
End If
ConvertNumber = Val(sTempNum)
End Function
without rounding....
Private Function ConvertNumber(ByRef pdNum As Double) As Double
' this is without rounding
Dim lTempWhole As Long
lTempWhole = Fix(pdNum)
ConvertNumber = (lTempWhole * 1000) + (Fix((pdNum - lTempWhole) * 1000) \ 1)
End Function
Without rounding....
Private Function ConvertNumber(ByRef pdNum As Double) As Double
' this is without rounding
Dim lTempWhole As Long
lTempWhole = Fix(pdNum)
ConvertNumber = (lTempWhole * 1000) + ((pdNum - lTempWhole) * 1000 \ 1)
End Function
Using string with no rounding.....
Private Function ConvertNumber(ByRef pdNum As Double) As Double
' this is without rounding
Dim sTempNum As String
Dim iDecPos As Integer
sTempNum = Str(pdNum)
iDecPos = InStr(sTempNum, ".")
If iDecPos > 1 Then
sTempNum = Mid(sTempNum, 1, iDecPos - 1) & Mid(sTempNum, iDecPos + 1, 3)
ElseIf iDecPos = 1 Then
sTempNum = Mid(sTempNum, 2, 3)
End If
ConvertNumber = Val(sTempNum)
End Function
ASKER
traygreen,
A couple of points. Why have you given me 3 functions called ConvertNumber ?. I've assumed these are 3 different functions to be called in turn with the results of the previous one. If this
wrong then please let me know how I should be running it.
If this is correct then I still dont quite see how this is helping me as I just end up with a Double with a host of trailing zeros when in effect what I need is leading zeros or leading spaces i,e 7.14 to become " 7140" in a string. And also I'm getting an overflow problem if the number is
of a certain size (>900000, i think). And my accuracy after the
decimal point goes berserk e.g, 12.2 becomes 12199000.
Anyway, thanks for taking an interest in my problem,
ED
A couple of points. Why have you given me 3 functions called ConvertNumber ?. I've assumed these are 3 different functions to be called in turn with the results of the previous one. If this
wrong then please let me know how I should be running it.
If this is correct then I still dont quite see how this is helping me as I just end up with a Double with a host of trailing zeros when in effect what I need is leading zeros or leading spaces i,e 7.14 to become " 7140" in a string. And also I'm getting an overflow problem if the number is
of a certain size (>900000, i think). And my accuracy after the
decimal point goes berserk e.g, 12.2 becomes 12199000.
Anyway, thanks for taking an interest in my problem,
ED
I gave you three different options.
It sounds as if the one you want to go with is the first string solution...
>>If this is correct then I still dont quite see how this is helping me as I just end up with a Double with a host of trailing zeros when in effect what I need is leading zeros or leading spaces i,e 7.14 to become " 7140" in a string.
Use the following....
Private Function ConvertNumber(ByRef pdNum As Double) As Double
' this is without rounding
Dim sTempNum As String
Dim sDecNum As String
Dim iDecPos As Integer
Dim icv As Integer
sTempNum = Str(pdNum)
iDecPos = InStr(sTempNum, ".")
If iDecPos > 1 Then
sDecNum = Mid(sTempNum, iDecPos + 1, 3)
sTempNum = Mid(sTempNum, 1, iDecPos - 1)
ElseIf iDecPos = 1 Then
sDecNum = Mid(sTempNum, 2, 3)
sTempNum = ""
Else
sDecNum = "000"
End If
' pad the decimal number to 3 positions
For icv = Len(sDecNum) To 2
sDecNum = sDecNum & "0"
Next icv
ConvertNumber = sTempNum & sDecNum
End Function
This will get you the string without the decimal
It sounds as if the one you want to go with is the first string solution...
>>If this is correct then I still dont quite see how this is helping me as I just end up with a Double with a host of trailing zeros when in effect what I need is leading zeros or leading spaces i,e 7.14 to become " 7140" in a string.
Use the following....
Private Function ConvertNumber(ByRef pdNum As Double) As Double
' this is without rounding
Dim sTempNum As String
Dim sDecNum As String
Dim iDecPos As Integer
Dim icv As Integer
sTempNum = Str(pdNum)
iDecPos = InStr(sTempNum, ".")
If iDecPos > 1 Then
sDecNum = Mid(sTempNum, iDecPos + 1, 3)
sTempNum = Mid(sTempNum, 1, iDecPos - 1)
ElseIf iDecPos = 1 Then
sDecNum = Mid(sTempNum, 2, 3)
sTempNum = ""
Else
sDecNum = "000"
End If
' pad the decimal number to 3 positions
For icv = Len(sDecNum) To 2
sDecNum = sDecNum & "0"
Next icv
ConvertNumber = sTempNum & sDecNum
End Function
This will get you the string without the decimal
To pad to fixed length on the leading side, replace...
ConvertNumber = sTempNum & sDecNum
with...
stempnum = sTempNum & sDecNum
stempnum = space(20 - len(stempnum)) & stempnum
ConvertNumber = stempnum
You can replace the number 20 with whatever length you'd like or with a constant.
Lots of luck,
Tray
ConvertNumber = sTempNum & sDecNum
with...
stempnum = sTempNum & sDecNum
stempnum = space(20 - len(stempnum)) & stempnum
ConvertNumber = stempnum
You can replace the number 20 with whatever length you'd like or with a constant.
Lots of luck,
Tray
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Absolutely Perfect.
Cheers.
Cheers.
Sorry for the hassles on that one. I am glad I could make it work for you : )
ASKER