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.
eryanAsked:
Who is Participating?
 
traygreenConnect With a Mentor Commented:

 
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
   
   stempnum = sTempNum & sDecNum
   stempnum = space(20 - len(stempnum)) & stempnum
   ConvertNumber = stempnum

End Function

This will get you the string without the decimal  
 
You can replace the number 20 with whatever length you'd like or with a constant.

Lots of luck,
Tray      

0
 
eryanAuthor Commented:
Edited text of question
0
 
eryanAuthor Commented:
Edited text of question
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
eryanAuthor Commented:
Edited text of question
0
 
traygreenCommented:
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

0
 
traygreenCommented:
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

0
 
traygreenCommented:
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

0
 
eryanAuthor Commented:
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
0
 
traygreenCommented:
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

0
 
traygreenCommented:
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

0
 
eryanAuthor Commented:
Absolutely Perfect.
Cheers.

0
 
traygreenCommented:
Sorry for the hassles on that one.  I am glad I could make it work for you : )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.