Concaténer le même champ de plusieurs enregistrements
Une question qui revient fréquemment sur les forums et sur le blog : j’ai une table de personnes avec par exemple un champ
Nom
. Je souhaiterais obtenir la concaténation de tous les noms de personnes. En d’autres termes, reprendre le nom de chaque ligne, et l’ajouter aux noms précédents. Comment faire ?
Principe
Pour parcourir plusieurs enregistrements et leur appliquer une opération spécifique – ici la concaténation -, il faut passer un objet Recordset en VBA.
Et tant qu’à écrire du code, on va en profiter pour personnaliser (un peu) le résultat : il sera possible de choisir le nombre de champs à concaténer, ainsi que les délimiteurs de lignes et de colonnes. Tout ça sera plus clair dans la suite, suivez le guide ! 🙂
Voici la table qui servira d’exemple dans cet article :
Le code
Recopiez ce qui suit dans un module standard de votre base de données.
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
' --- ' FUSION DE VALEURS D'ENREGISTREMENTS ' --- ' Entrée : strSource <- Table, requête ou instruction SQL ' varFields <- Liste des champs à fusionner. ' Ex. : array("Nom", "Prénom") ' strRowDelimiter <- Séparateur pour les lignes. ' strFieldDelimiter <- Séparateur pour les colonnes. ' blnIncludeNulls <- True pour conserver les valeurs Null. ' Sortie : Résultat concaténé ' Function MergeRows( _ ByVal strSource As String, _ ByVal varFields As Variant, _ Optional ByVal strRowDelimiter As String = ", ", _ Optional ByVal strFieldDelimiter As String = " ", _ Optional ByVal blnIncludeNulls = False) As String ' Quelques variables Dim rst As DAO.Recordset Dim varField As Variant Dim strResult As String Dim strVal As String ' Ouvrir la source Set rst = CurrentDb.OpenRecordset(strSource, dbOpenSnapshot) ' Concaténer la valeur du même champ, pour tous les enregistrements ' de la source strResult = "" While Not rst.EOF ' Concaténer tous les champs strVal = "" For Each varField In varFields If strVal <> "" Then strVal = strVal & strFieldDelimiter strVal = strVal & rst(varField) Next strVal = Trim(strVal) ' Ajout de l'enregistrement au résultat If (strVal <> "") Or blnIncludeNulls Then If strResult <> "" Then strResult = strResult & strRowDelimiter strResult = strResult & strVal End If ' Enregistrement suivant rst.MoveNext Wend ' Libérer les ressources rst.Close Set rst = Nothing MergeRows = strResult End Function |
Comme vous le voyez, la fonction reçoit 5 paramètres, dont seuls les deux premiers sont obligatoires :
- Le premier paramètre est le nom de la table à traiter. Il peut en fait s’agir d’une table, d’une requête, ou d’une instruction SQL complète (du type :
SELECT * FROM [Ma table] WHERE..."
). - Le deuxième paramètre est un tableau (
Array
) donnant la liste des champs à concaténer. - Le troisième paramètre permet de définir le délimiteur de lignes. En d’autres termes la séquence de caractères qui séparera chaque ligne de table. Cette valeur est facultative, elle vaut par défaut
", "
(virgule puis espace). - Le quatrième paramètre permet de définir le délimiteur de colonnes (ou de champs, c’est la même chose). A savoir la séquence de caractères qui séparera les différents champs d’une même ligne. Cette valeur est facultative, elle vaut par défaut
" "
(espace). - Enfin, le dernier paramètre doit valoir
True
ouFalse
. Il permet d’intégrer ou non les valeurs vides de chaque ligne. Par défaut, ces valeurs sont ignorées. On considère qu’il faut ignorer des valeurs si l’ensemble des champs concaténés, sur une même ligne, sont vides.
Utilisation de base
Pour tester la fonction :
- Faites apparaître la fenêtre Exécution (
[Ctrl]
+G
). - Tapez les lignes qui suivent dans cette fenêtre.
- Validez par la touche
[Entrée]
.
Pour que la fonction retourne un résultat, il faut lui fournir au moins 2 paramètres : la source de données (table, requête ou instruction SQL), et la liste des champs à traiter. Ce qui donne, dans la fenêtre Exécution :
1 |
? MergeRows("tbl Destinataires", array("Nom")) |
Inisan, Zeblouze, Versaire, Versaire, Gates, Ballmer, Skywalker, Skywalker
Dans cet exemple, un seul champ est traité.. Pour ajouter le prénom, faites ceci :
1 |
? MergeRows("tbl Destinataires", array("Nom", "Prénom")) |
Inisan Hervé, Zeblouze Agathe, Versaire Laure, Versaire Elsa, Gates, Ballmer, Skywalker Luke, Skywalker Anakin
Personnalisation des délimiteurs
Vous voyez que le délimiteur de lignes est la virgule (suivie d’un espace), et que le délimiteur de colonnes est l’espace simple. Pour modifier ces deux délimiteurs, vous pouvez essayez ça :
1 |
? MergeRows("tbl Destinataires", array("Nom", "Prénom"), "/", ".") |
Inisan.Hervé/Zeblouze.Agathe/Versaire.Laure/Versaire.Elsa/Gates./Ballmer./Skywalker.Luke/Skywalker.Anakin
Ou ça :
1 |
? MergeRows("tbl Destinataires", array("Nom", "Prénom"), vbCrlf) |
Inisan Hervé Zeblouze Agathe Versaire Laure Versaire Elsa Gates Ballmer Skywalker Luke Skywalker Anakin
La constante vbCrLf
(ou sa petite soeur vbCr
) désigne un retour à la ligne. On s’en sert ici comme séparateur de lignes, justement, ce qui fait que les lignes s’affichent verticalement (mais toujours dans une seule chaîne de caractères).
Intégration des valeurs vides (null)
Si vous regardez attentivement la table de départ, vous noterez qu’une personne n’a ni nom ni prénom. Cette personne est ignorée par défaut de tous les résultats précédents. Si vous souhaitez quand même la prendre en compte, modifiez le dernier paramètre de la fonction MergeRows()
, de cette manière :
1 |
? MergeRows("tbl Destinataires", array("Nom", "Prénom"), ", ", " ", True) |
Inisan Hervé, Zeblouze Agathe, Versaire Laure, Versaire Elsa, Gates, Ballmer, Skywalker Luke, , Skywalker Anakin
Cette fois, il y a 2 virgules entre Luke et Anakin Skywalker. L’emplacement vide entre les 2 virgules est la concaténation du nom et du prénom vides. Cette option ne devrait pas servir souvent, mais au cas où… 😉
Notez que si vous aviez choisi comme champs array("Nom", "Prénom", "Email")
, notre homme invisible serait sorti dans les résultats de toute façon, et indépendamment du paramètre blnIncludeNulls = True
… puisque son adresse email est renseignée.
Utilisation des paramètres nommés
Comme dans tout VBA, vous pouvez utiliser le nom des paramètres, plutôt que leur position, pour (parfois) simplifier le code. Dans l’exemple précédent, j’ai utilisé la position de 1 à 5 des paramètres pour leur donner une valeur. Mais ça m’a obligé de redéfinir les délimiteurs de lignes et de colonnes (paramètres 3 et 4), pour alimenter le cinquième paramètre à True
.
Pour éviter de redéfinir les arguments 3 et 4, j’aurais pu écrire aussi :
1 |
? MergeRows(strSource:="tbl Destinataires", varFields:=array("Nom", "Prénom"), blnIncludeNulls:=true) |
Inisan Hervé, Zeblouze Agathe, Versaire Laure, Versaire Elsa, Gates, Ballmer, Skywalker Luke, , Skywalker Anakin
Types de sources
Comme il a été dit en début d’article, le premier paramètre de la fonction peut être :
- Une table quelconque de votre base de données (ex. :
"tbl Destinataires"
) - Une requête enregistrée
quelconque (ex. :"rqt Destinataires par ordre alphabétique"
). - Une instruction SQL
SELECT
.
Dans ce dernier cas, on peut se permettre des choses intéressantes, comme filtrer les données avant concaténation. Dans une procédure VBA (mais cette fois plus dans la fenêtre Exécution), vous pourriez écrire :
1 2 3 4 5 6 7 8 |
Sub TestFusion() Dim strSQL As String strSQL = "SELECT * FROM [tbl Destinataires]" _ & " WHERE Nom LIKE 'V*'" _ & " ORDER BY Nom, Prénom" Debug.Print MergeRows(strSQL, Array("Nom", "Prénom")) End Sub |
Et vous obtiendriez ceci dans la fenêtre Exécution :
Versaire Elsa, Versaire Laure
Pas forcément spectaculaire, mais si vous regardez bien, par rapport aux exemples précédents :
- Seules 2 personnes sont extraites.
- Elles sont triées par
Nom
etPrénom
. - Non rien… 😉
Le mot de la fin
Dans cet article, on n’a traité que des exemples bruts, dans l’environnement VBA. Mais la fonction MergeRows()
, comme toutes les fonctions VBA, peut bien sûr être utilisée ailleurs : dans les requêtes graphiques, dans les formulaires et les états notamment. Ça fera peut-être l’objet d’un autre article, si vous êtes intéressés.
Pour autant, il ne faut pas l’utiliser à toutes les sauces ! La fonction n’affiche que des données brutes, avec peu d’options de présentation. Si la mise en forme est importante, pensez peut-être à des approches « Sous-formulaire ou sous-état ».
Bonjour
Novice en vba et n’ayant malheureusement qu’access 2000 à dispo, j’ai essayé d’appliquer ce code à ma base de donnée et évidemment cela ne fonctionne pas.
J’ai une requête qui reprend des individus ayant différentes parcelles cadastrales sur plusieurs et parfois même commune. Mon but était bien de concaténer pour un même individu et une même commune les différentes parcelles cadastrales dans un même champ.
Je me heurte d’une part au fait que le DAO.Recordset n’est pas reconnu, je l’ai donc modifié par ADODB.Recordset mais après cela bug sur une autre ligne de code à savoir:
Set rst = CurrentDb.OpenRecordset(strSource, dbOpenSnapshot)
Auriez-vous une idée de comment passer outre ou une autre idée pour avoir cette fonctionnalité via peut-être une requête?
J’ai essayé en passant par une requête analyse croisée puis une concatenation des champs créés mais ce n’est pas simple, d’autant que la base doit servir à une autre personne que moi qui n’y connais absolument rien et qui veux simplement cliquer sur un bouton et sortir un état complet!
Donc si vous avez une idée je suis preneuse!
Merci
Il y a 2 bibliothèques d’objets « concurrentes » fournies par Microsoft : DAO et ADO (merci Microsoft pour les abréviations ! ;)).
La syntaxe ADO est très différente de DAO, et ne peut donc pas être juste remplacée (il n’y a par exemple par de
CurrentDb
sur ADO).Ce qu’il faut faire : aller dans Outils / Références, puis cocher la bibliothèque DAO (quelque chose comme : « Microsoft Data Access Object Library » ; sur un Access récent : « Microsoft Office 16.0 Access Database Engine Object Library »).
Bonjour,
Merci, j’ai enfin un retour dans le test exécution. Je n’avais pas la version 16.0 mais 14.0 mais cela fonctionne quand même.
Encore un gros merci
Denis > La syntaxe pour appeler
MergeRows()
fait appel à du code spécifique à VBA, qui ne peut pas être utilisé directement dans une zone de texte (leArray()
notamment).Une solution pourrait être d’écrire cette fonction dans un module standard :
Puis ensuite écrire ceci dans la zone de texte :
Mais je ne suis pas sûr que ce soit utile d’afficher les emails sur le formulaire. Parce que si l’objectif est l’envoi de mails, il faudra sans doute obtenir les emails dans VBA directement, avant d’envoyer le message.
Hervé > J’ai créé une zone de texte et dans la source de contrôle j’ai tappé la formule : =MergeRows(« RQT_Select_Mail »;Array(« Email »); »; « ). Le champ Email est bien de type texte.
Mon but est de créer un formulaire de sélection d’e-mails pour un envoi à plusieurs destinataires via outlook.
Hervé > J’ai créé une zone de texte et utilisé votre fonction dans la source de contrôle. J’essaie de concaténer des adresses e-mails de type texte pour l’utiliser dans outlook par la suite pour un envoi à plusieurs destinataires. Il y a certainement une manière plus simple d’y arriver?
Denis > La formule est utilisée de quelle manière sur le formulaire ?
Bonjour,
Je vous remercie pour votre article très clair et détaillé.
Si j’utilise cette fonction avec une zone de texte dans un formulaire, cela affiche la valeur « #Type! ». Est-ce que vous auriez une solution?