Discussion :

[céleste]

Bonjour à toutes et à tous,
Je suis un petit nouveau sur ce site et j'ai un gros problème de formule
Je veux procéder à un contrôle de données bancaires pour une application ACCESS développée en interne
- Retrouver la clé BIC, pas de problème
- Calculer la clé RIB, quelques soucis au départ mais ça fonctionne, même si la longueur de la requête fait que le résultat met du temps à venir, je verrais pour optimiser ça à l'occasion
Là où mon problème se pose, c'est que je n'arrive pas à calculer la clé Iban, j'ai chercher partout sur le net et je n'ai rien trouvé de satisfaisant pour répondre à cette question.
J'ai bien retranscrit mes lettres en chiffres, mais une fois avec ce gros chiffre, comment je le travaille ?
VBA, je ne suis pas trop à l'aise, je suis donc à la recherche d'un Dieu SQL ou même plusieurs qui auraient une requête de ce type

Je vous remercie beaucoup par avance

[pyloupylou]

hello,
en regardant un peu j'ai trouvé ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
Description du numéro IBAN
 
    Le numéro IBAN (International Bank Account Number) est constitué :
 
    d'un code pays composé de 2 lettres (FR pour la France, BE pour la Belgique, CH pour la Suisse, TN pour la Tunisie...),
    suivi d'une clé comprise entre 02 et 98 (les comptes français, s'ils ne sont constitués que de chiffres, ont tous la même clé IBAN, valant 76, ceci étant dû à la clé RIB qui est elle-même un modulo 97),
    suivi du numéro de compte BBAN (Basic Bank Account Number) qui a une longueur fixe pour un pays donné mais variable d'un pays à l'autre (30 caractères au maximum).
    Pour la France, ce numéro correspond aux 23 caractères du RIB
    (code banque + code guichet + numéro de compte + clé RIB)
 
Algorithme de calcul de la clé IBAN
 
    La valeur numérique sur laquelle porte le calcul de la clé du numéro IBAN est la concaténation du numéro de compte BBAN et du code pays dont les lettres ont été converties en leur équivalent numérique avec les valeurs suivantes :
 
        A = 10 ; B = 11 ; C = 12 ; ... etc ... ; Y = 34 ; Z = 35
 
    La clé peut alors être calculée avec la formule suivante :
 
        Clé IBAN = 98 - ( ( Valeur numérique ) modulo 97 )

du coup après avoir calculé ta valeur numérique tu peux faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
function calcul_cle_iban ( valeur_num as long ) as long
calcul_cle_iban=98-(valeur_num mod 97 )
end function

ensuite cette fonction tu l'utilises comme tu veux soit dans une requete soit dans un module

[ClaudeLELOUP]

Bonjour,

Voici un jeu de fonctions qui convertit un N° de compte bancaire en code IBAN.
En principe, elle fonctionne quel que soit le pays.

Exemple d’utilisation dans la fenêtre d’exécution



N.B. Les lettres du pays en majuscules ou en minuscules.

Ce code dans un module

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Option Compare Database
Option Explicit
 
Public Function BBANtoIBAN(CodePays As String, BBAN As String) As String
'Ex. :   ?   BBANtoIBAN("be","210076597417")
  Dim NbrePays As String
  Dim Base As String
  Dim CleControle As String
  'Transformation CodePays en Nbre
  NbrePays = Asc(Left(UCase(CodePays), 1)) - 55 & Asc(Right(UCase(CodePays), 1)) - 55
  'Base de calcul de la clé de contrôle Pays
  Base = BBAN & NbrePays & "00"
  'Calcul de la clé de contrôle pays
  CleControle = 98 - RestePar97(Base)
  'Retour
  BBANtoIBAN = UCase(CodePays) & Format(CleControle, "00") & " " & Format(BBAN, "@@@@ @@@@ @@@@")
End Function
 
 
Function RestePar97(Nbre As String) As Integer
'La fonction Mod ne fonctionne pas au delà de 2147483647
'un N° de compte bancaire est parfois supérieur
'd'où la nécessité d'écrire cette fonction.
 
  Dim i As Integer
  RestePar97 = 0
  For i = 0 To Len(Nbre) - 1
    RestePar97 = (RestePar97 * 10 + CInt(Mid(Nbre, i + 1, 1))) Mod 97
  Next i
End Function

Ce serait sympa de confirmer que cette fonction valable pour la Belgique* l'est aussi pour votre pays.


* En Belgique, les N° bancaires sont à 12 chiffres.

[céleste]

Bonjour PylouPylou
Bonjour Claude

Merci à tous les deux pour vous être intéressés à ma question
Vos réponses sont plus sur du Vba où je ne suis pas du tout à l'aise, car si je prends votre code et que je le place dans un module, je ne sais pas trop comment le modifier ni même comment l'appeler après pour qu'il interroge ma table. Je suis loin très loin d'être aussi bon que vous
Voici le début de ma requête : (vous me direz ce que vous en pensez)

SELECT ([BANQUE1]+[CODGUI1]+[NUMCPT1]+[CLERIB1]+ 'FR' + '00') AS CODEIBAN, CLERIB1, CLEIBA1, Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(CODEIBAN,'A','10'),'B','11'),'C','12'),'D','13'),'E','14'),'F','15'),'G','16'),'H','17'),'I','18'),'J','19'),'K','20'),'L','21'),'M','22'),'N','23'),'O','24'),'P','25'),'Q','26'),'R','27'),'S','28'),'T','29'),'U','30'),'V','31'),'W','32'),'X','33'),'Y','34'),'Z','35') AS iban,



98-(int((iban)/97)-10) AS calcul, left(iban, 9)/97 AS lefiban, mid(iban, 10, 9) AS midiban, right(iban, 9) AS rightiban, Int(Len(iban) / 3) AS D, Int(Left(iban, D)/97) AS E, int(Left(Right(iban, 20), D))/97 AS F, int(E & F)/97 AS EF, Int(Right(iban, Len(iban) - 2 * D)) AS G, int((EF & G)/97), 98 - (((((left(iban, 9)/97) & mid(iban,10, 9))/97) & mid(iban,19, 11))/97)

FROM MaTable;

C'est la partie en gras : calcul du modulo qui me pose problème, je patauge sévèrement

Avez-vous une solution à celà ?

Je vous remercie infiniment par avance
Cordialement

[ClaudeLELOUP]

Bonjour Céleste,

C'est beaucoup plus simple que tu ne le penses !

Oublie cette requête. Que veux-tu faire exactement ?

Dans quelles circonstances as-tu besoin du code IBAN ?

[céleste]

Re bonjour et merci

En fait j'en ai besoin pour contrôler la clé Iban qui a été fournit ou pour la renseigner si elle n'a pas été fournit

Que me proposez-vous ?

Merci d'avance

[ClaudeLELOUP]

Dans un formulaire ?

[céleste]

Oui c'est exactement celà
Vous lisez dans mes pensées

[ClaudeLELOUP]

OK, je dois m'absenter quelques heures, à mon retour, je te proposerai un exemple.

Entretemps peux-tu vérifier que la fonction proposée fonctionne correctement avec un N° bancaire français.

Tu loges le code dans un module,

ensuite <Alt + F11> pour ouvrir la fenêtre d'exécution,

tu saisis : « ? BBANtoIBAN("FR","UnN°Banque" » et <Enter>.

[céleste]

ça fonctionne après avoir tapé entrée, ça me donne
--> j'ai saisi à la suite code banque + code guichet + numcompte + clé

Print BBANtoIBAN("FR", "le_n°_sur23chiffres")

j'ai hâte de voir le formulaire que tu proposes

[ClaudeLELOUP]

Lorsque tu entres un N° bancaire, l’IBAN s’imprime en dessous.
Si tu rentres un N° IBAN correct, il s’allume en vert,



Si non correct, en rouge




Code associé à l’événement « Après mise à jour » de txtNum :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Option Compare Database
Option Explicit
 
Private Sub txtNum_AfterUpdate()
  'Contrôle code Pays
  If IsNull(Me.txtPays) Then
      MsgBox "Code Pays manquant"
      Exit Sub
  End If
  'Aménager txtIBAN
  Me.txtIBAN = BBANtoIBAN(Me.txtPays, Me.txtNum)
 
End Sub

Mise en forme conditionnelle de ctlIBAN :

[ClaudeLELOUP]

Plus de détails ici : http://claudeleloup.developpez.com/t...l-du-code-iban

[céleste]

Bonjour Claude et merci pour cette solution qui est est bien meilleure en terme d'exécution qu'une requête (que j'ai finalement réussi à écrire et à faire fonctionner)
Je préfère ta solution
Que dois je faire pour qu'elle interroge la table complète plutôt que de rentrer la donnée manuellement ?

[ClaudeLELOUP]

Bonjour,

Tu veux dire que tu as une table qui contient dans une colonne le N° bancaire local et que tu voudrais compléter une autre colonne avec l'IBAN ?

Si oui : donne le nom de la table et celui des deux colonnes.
Si non : décris ce que tu veux faire.

[céleste]

Bonjour Claude

Ma table contient un certain nombre de données notamment les données bancaires, à savoir, les champs :
titulairecompte, codebanque, codeguichet, numerocompte, clerib, cleiban, code bic, pays

La clé iban est ou n'est pas renseignée, et parfois mal, d'où ce besoin de contrôle

En fait j'aurais besoin de quelque chose qui me liste l'ensemble de ces données, avec une colonne supplémentaire ajoutée à droite du champ cléiban qui recalcule cette clé et (cerise sur le gateau) une colonne supllémentaire qui me compare ces deux champs avec vrai ou faux en réponse

Bien cordialement

[ClaudeLELOUP]

Bonjour,

Peux-tu placer un échantillon de ta table dans la BdD jointe et reposter sans modifier la version (.mdb).

[céleste]

Bonjour Claude

Désolé pour ce retard
Pour les données, je suis un peu géné dans la mesure où ce sont des données sensibles que je ne peux pas communiquer tu t'en doutes
Si tu peux déposer un modèle que je testerais, ce sera plus sage

merci d'avance

[ClaudeLELOUP]

Bonjour,

Pour les données, je suis un peu gêné dans la mesure où ce sont des données sensibles

Mais rien ne t'oblige à révéler des données sensibles, il suffit que tu y mettes une table avec les seules colonnes utiles
et des données « anonymisées » qui te permettront de tester.