Convert 23-character- long string to number. What datatype to use?

[natha]

Hi,

Following fields are in my db:

[CodeBanque], char, 5
[CodeGuichet], char, 5
[NoCompte], char, 11
[CleRIB], char, 2

I need to concatenate those 4 fields and apply modulo 97 for Bank Account validation check. A msgbox must then appear with the result. (If = 0 it's OK. Else not.)

The problem is concatenating gives me a number of 23 digits.

I didn't find any datatype fitting for such long number. I always get "Overflow" message from Access.

This what I do:

Dim CheckAlgo
CheckAlgo = CLng(txtCodeBanque & txtCodeGuichet & txtNoCompte & txtCleRIB) Mod 97
If CheckAlgo = 0 Then
MsgBox "OK."
Else: MsgBox "Check the numbers again."
End If

I would be very happy to have this solved.
Thanx to everyone.

Natha

 

[zevw]

Try Val(<your conctenated string>) it will convert it to a value.

 

[CajunCenturion]

You will not be able to use the Mod function, but rather, you'll have to write your own Mod function and do your own concatenation. To deal with numbers that large, you also have to use the CDec function and the variant data type, and do your own numerical based string concatenation. Otherwise, you'll lose precision by reverting into scientific notation. Consider the following example:

Dim BigNum As Variant
Dim BigNum1 As Variant
Dim BigNum2 As Variant
   
Dim char1 As String
Dim char2 As String
Dim char3 As String
Dim char4 As String
  
char1 = "12345"
char2 = "67890"
char3 = "12345678901"
char4 = "23"

'Numberically Concatenate the string together, most significant char string first
  
BigNum = CDec(char4)
BigNum = CDec(BigNum) + (CDec(char3) * (10 ^ Len(Trim(BigNum))))
BigNum = CDec(BigNum) + (CDec(char2) * (10 ^ Len(Trim(BigNum))))
BigNum = CDec(BigNum) + (CDec(char1) * (10 ^ Len(Trim(BigNum))))

' Now do your own Mod function

BigNum1 = CDec(BigNum) / CDec(97)
BigNum2 = Fix(CDec(BigNum1))
BigNum = CDec(BigNum1) - CDec(BigNum2)

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[natha]

Thanks to both of you for the prompt respond.

CajunCenturion, your numberically concatenation is not accurate since I may have in char3 &quot;00000212355&quot;, so I will be missing the leading zeros.

What I did is replaceing this:

BigNum = CDec(char4)
BigNum = CDec(BigNum) + (CDec(char3) * (10 ^ Len(Trim(BigNum))))
BigNum = CDec(BigNum) + (CDec(char2) * (10 ^ Len(Trim(BigNum))))
BigNum = CDec(BigNum) + (CDec(char1) * (10 ^ Len(Trim(BigNum))))

by this:

BigNum = CDec(char1 & char2 & char3 & char4)

and it works.

 

[CajunCenturion]

That's great natha. Glad it's working.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein