The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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.

' 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

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

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

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

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

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

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.

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