Solved

Manipulation of Numbers.

Posted on 1999-01-06
12
148 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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 100 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now