Solved

# Manipulation of Numbers.

Posted on 1999-01-06
148 Views
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
Question by:eryan
• 7
• 5

Author Comment

ID: 1454324
Edited text of question
0

Author Comment

ID: 1454325
Edited text of question
0

Author Comment

ID: 1454326
Edited text of question
0

LVL 3

Expert Comment

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

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

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

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

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

ID: 1454332
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

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

ID: 1454334
Absolutely Perfect.
Cheers.

0

LVL 3

Expert Comment

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

## Featured Post

### Suggested Solutions

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â€¦