Thanks,
Sorry, I posted it on an Excel-newsgroup but it should work in Access. ( The
response is much faster in Excel-newsgroups)
My solution now is limited to Belgium ( stays inside VBA's level ) and the
user gets a msg that there is no check on foreign bank nummers.
For the present Company that is no problem.
I have now 3 functions to check IBAN and Check Bic individualy and one to
find the Bic from the IBAN in a table (BicFromPrefix) I downloaded from the
national Bank Belgium !
Filip
-------------------------------------------------------------------------------------------
Public Function CheckIBAN(ByVal strIban As String) As Boolean
'1. BE62510007547061
'2. 510007547061 BE62
'3. 510007547061111462
'4. De modulus 97 (remainder of div 97) 510007547061111462 /97 remainder =
1
On Error GoTo Errhandling
Dim strTemp As String
Dim strAllDigits As String
Dim strToken As String
Dim iLoop As Integer
Dim vDecTotalNum As Variant
Dim iModulus As Long
CheckIBAN = False
strTemp = Replace(strIban, " ", "")
strTemp = right$(strTemp, Len(strTemp) - 4) & Left$(strTemp, 4)
For iLoop = 1 To Len(strTemp)
strToken = UCase(Mid$(strTemp, iLoop, 1))
If Not IsNumeric(strToken) Then
If InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", strToken) = 0 Then
Exit For
Else
strToken = CStr(Asc(strToken) - 55)
End If
End If
strAllDigits = strAllDigits & strToken
Next iLoop
vDecTotalNum = CDec(strAllDigits) / 97
iModulus = (vDecTotalNum - Int(vDecTotalNum)) * 97
If iModulus = 1 Then CheckIBAN = True
Exit Function
Errhandling:
MsgBox err.Number & " " & err.Description
End Function
----------------------------------------------------
Public Function CheckBicBelgium(ByVal strBic As String, ByVal strIban As
String) As Boolean
On Error GoTo Errhandling
Dim strTemp As String
Dim strBankPrefixNum As String
Dim strBicFromList As String
CheckBicBelgium = False
strTemp = Replace(strIban, " ", "")
strBankPrefixNum = Mid$(strTemp, 5, 3)
strBicFromList = DLookup("Biccode", "BicFromPrefix",
"((BicFromPrefix.T_Identification_Number)='" & strBankPrefixNum & "')")
MsgBox strBankPrefixNum & " " & strBicFromList
If strBic = Replace(strBicFromList, " ", "") Then CheckBicBelgium = True
Exit Function
Errhandling:
MsgBox err.Number & " " & err.Description
End Function
---------------------------------------------------------------------------
Public Function FindBicBelgium(ByVal strIban As String) As String
On Error GoTo Errhandling
Dim strTemp As String
Dim strBankPrefixNum As String
strTemp = Replace(strIban, " ", "")
strBankPrefixNum = Mid$(strTemp, 5, 3)
FindBicBelgium = DLookup("Biccode", "BicFromPrefix",
"((BicFromPrefix.T_Identification_Number)='" & strBankPrefixNum & "')")
FindBicBelgium = Replace(FindBicBelgium, " ", "")
Exit Function
Errhandling:
MsgBox err.Number & " " & err.Description
End Function
-----------------------------------------------------------------
Post by Filips BenoitHey,
Ik zoek vba-code om oude bankrekeningnummers om te zetten tot IBAN en BIC
mvg,
Filip