Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Manipulation of Numbers.

Posted on 1999-01-06
12
Medium Priority
?
164 Views
Last Modified: 2010-05-03
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.
0
Comment
Question by:eryan
  • 7
  • 5
12 Comments
 

Author Comment

by:eryan
ID: 1454324
Edited text of question
0
 

Author Comment

by:eryan
ID: 1454325
Edited text of question
0
 

Author Comment

by:eryan
ID: 1454326
Edited text of question
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:traygreen
ID: 1454327
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
 
LVL 3

Expert Comment

by:traygreen
ID: 1454328
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
 
LVL 3

Expert Comment

by:traygreen
ID: 1454329
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
 

Author Comment

by:eryan
ID: 1454330
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
 
LVL 3

Expert Comment

by:traygreen
ID: 1454331
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
 
LVL 3

Expert Comment

by:traygreen
ID: 1454332
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
 
LVL 3

Accepted Solution

by:
traygreen earned 400 total points
ID: 1454333

 
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
 

Author Comment

by:eryan
ID: 1454334
Absolutely Perfect.
Cheers.

0
 
LVL 3

Expert Comment

by:traygreen
ID: 1454335
Sorry for the hassles on that one.  I am glad I could make it work for you : )
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question