Formules mathématiques dynamiques avec Microsoft ACCESS

On reproche souvent à ACCESS de ne pas être souple en matière de formules mathématiques. En effet, le comparer à EXCEL est impossible, chaque produit a son utilité. C'est la raison pour laquelle l'automation permet des traitements qui sont difficiles, voire impossibles à reproduire avec ACCESS. Comme toute affirmation a son contraire, nous allons démontrer, avec certaines limites, que nous pouvons créer des formules mathématiques dynamiques avec ACCESS. Dynamiques dans le sens où elles peuvent être entrées directement par l'utilisateur.

Pour la démonstration, nous allons résoudre un problème souvent rencontré dans la gestion de contrats à moyen et long terme. Ce type de gestion est généralement assorti d'une clause de revalorisation contractuelle. En résumé, chaque année, à date anniversaire du contrat, il est convenu d'une revalorisation indexée sur des indices officiels. Pour la France, vous pouvez consulter la majorité de ceux-ci sur le site de l’INSEE. On utilise le ratio d'un indice constaté entre la date actuelle et la même date un an plus tôt. Les contrats de location d'habitations en sont un parfait exemple.

II-A. Analyse rapide▲

II-B. Le prérequis▲

II-C. Les données▲

Deux solutions s'offrent à nous, la première est de passer par Excel, de lui laisser faire les calculs puis de réintégrer les résultats dans la base ACCESS ; très simple quand il s'agit d'une formule avec une seule date. Mais que se passe-t-il lorsque vous avez 300 contrats, 10 formules différentes et presque autant de dates de révision ?
Tout faire passer dans une feuille Excel préprogrammée ?
Un peu lourd et surtout long et sujet à problèmes.

III-A. MS ACCESS sur un air d'EXCEL ou comment créer son propre moteur de calcul▲

Public Function Calcule(cFormule as String) as Variant

End Function

Calcule = Sqr(4)

? calcule("")

III-B. Alimentez le moteur de calcul▲

Calcule = sqr(4)

Calcule = Eval("sqr(4)")

? Calcule("")

III-C. Quelques mots sur la fonction EVAL▲

Eval("msgbox ""bonjour""")

Eval("msgbox(""Bonjour"")")

III-D. Concrétisez le moteur de calcul▲

Calcule = Eval("Sqr(4)")

Calcule = Eval(cFormule)

? calcule("sqr(4)")

? calcule("sqr(8)")
? calcule("Year(Date)-1")

Il existe plusieurs méthodes pour lire le contenu d'une table ; le dlookup ou le recordset. Utilisons le recordset. Allez dans le menu Outils/Références et sélectionnez Microsoft DAO 3.6 Object Library.
Créez une nouvelle fonction que nous appellerons LireFormules().

Public Function LireFormules(idFormule As Integer) as string
' idFormule est le n° de la formule dans la table
'Auteur  Fabrice CONSTANS (loufab) http://loufab.developpez.com 2009
    Dim strCrit As String
    Dim rst As Recordset                                ' déclaration de l'objet
    Set rst = CurrentDb.OpenRecordset("tblFormules", dbOpenSnapshot)   ' initialisation du recordset
    With rst
        strCrit = .Fields(0).Name & " = " & idFormule        'composition du critère de recherche
        .FindFirst strCrit                                    ' recherche
        If Not .NoMatch Then Debug.Print Calcule(.Fields(1))    ' trouve et affiche le résultat
           .close                          ' fermeture
    End With
    Set rst = nothing                    ' libération
End Function

Dans la fenêtre d'exécution, testons cette nouvelle fonction. Tapez :

? LireFormules(1)
? LireFormules(2)

Nous sommes maintenant capables d'utiliser une formule issue d'une table.

Pour l'instant, nous évaluons une formule simple. Pour évaluer nos formules de revalorisation, il faut remplacer les indices par leur valeur respective. Nous avons besoin de 2 dates, la date de revalorisation et la date d'origine qui détermineront quels indices utiliser dans la période. L'intervalle étant fixé contractuellement, vous pourrez donc rencontrer des cas différents et de multiples formules.
On peut rencontrer plusieurs types d'indices, mais toujours par paire, selon la formule suivante.

IndiceOrigine / indiceActuel

Voici la fonction :

Function Revalorise(Montant As Currency, DateOrigine As Date, DateActuelle As Date, iFormule As Integer) As Currency
'Montant est le montant à revaloriser
'DateOrigine date d'origine ou date la plus ancienne
'DateActuelle date de revalorisation
'iFormule est le N° de la formule
'Auteur  Fabrice CONSTANS (loufab) http://loufab.developpez.com 2009

On Error GoTo Err_Revalorise

Dim fml As String           ' la formule
Dim FrstCar As Integer      ' Numéro du caractère traité
Dim Indice As String        ' abréviation de l'indice dans la formule
Dim valindice As Double        ' 

fml = LireFormule(iFormule)            ' on récupère la formule dans la table

fml = Replace(fml, "_Montant", Montant) ' on remplace le _Montant par sa valeur

Do
    FrstCar = 1
    While Not Mid(fml, FrstCar, 1) = "_"         ' fait défiler les chiffres, signe jusqu'au "_"
        FrstCar = FrstCar + 1
    Wend
    
    FrstCar = FrstCar + 1                        ' on se positionne sur le nom de l'indice
    
    While Mid(fml, FrstCar, 1) Like "[A-Z]"      ' tant que c'est une lettre on traite le nom de l'indice
        Indice = Indice & Mid(fml, FrstCar, 1)   ' on récupère l'indice
        FrstCar = FrstCar + 1
    Wend
    Indice = Indice & Mid(fml, FrstCar, 1)       ' pour la terminaison
                                                        ' 0 = indice ancien -> contrat
                                                        ' 1 = indice actuel -> tbl_indice
   
    If Right(Indice, 1) = 1 Then                 ' chercher l'indice 1 étant le plus ancien
       valindice = DLookup("Indice", "tblIndices", "Mois = " & Month(DateActuelle) & " And Annee=" & Year(DateActuelle))
    Else                          ' Indice contrat
       valindice = DLookup("Indice", "tblIndices", "Mois = " & Month(DateOrigine) & " And Annee=" & Year(DateOrigine))
    End If
    ' remplace dans la formule
    fml = Replace(fml, "_" & Indice, valindice, , 1) ' remplace 1 seule fois l'indice et son caractère de repérage.
    
    Indice = ""

Loop While InStr(1, fml, "_") > 0    'Il n'y a plus d'indice à traduire

fml = Replace(fml, ",", ".")   'remplace chaque virgule par un point
Revalorise = Calcule(fml)      ' retourne le résultat de l'opération.

Exit Function

Err_Revalorise:
If Err = 94 Then
   Revalorise = 0
   Exit Function
ElseIf Err = 6 Then
   fml = Replace(fml, ",", ".")   'remplace chaque virgule par un point
   Revalorise = Calcule(fml)
Else
   MsgBox Err & " -- " & Err.Description
   Revalorise = 0
   Exit Function
End If
End Function

V-A. Virgule ou point, le séparateur décimal▲

V-B. Quelques tests▲

? revalorise(100,"1/6/2007","1/6/2008",4)
105,5
? calcule("year(cdate(""2/8/2008""))-1")
2007
? calcule("replace(""c_est pas bon"",""_"",chr(149))")

il n'y a pas de secret, une fonction reste une fonction et à ce titre, elle peut évidemment être utilisée dans une requête. Ce qui, pour une revalorisation en masse, est un sérieux avantage.

Commençons par créer une petite table de « contrats » avec juste l'essentiel.

Remplissez le contrat en utilisant des numéros de formules existants (2) et dont les dates correspondent à celles saisies dans les indices (1/6/2007).

Créez ensuite la requête comme suit.

SELECT tblContrat.typeFormule, tblContrat.Montant, tblContrat.DernierReval, 
Revalorise([Montant],[DerniereReval],DateAdd("yyyy",1,[DerniereReval]),[TypeFormule])+tblContrat.Montant AS NouveauTarif
FROM tblContrat;

Vous avez noté que nous utilisons la formule Revalorisation() directement dans la requête et que ses paramètres sont les colonnes de la table.

La seule chose que nous pouvons commenter c'est la fonction native DateAdd() que vous pouvez retrouver dans le tutoriel, Les fonctions Date / Heure de Maxence Hubiche. Elle permet de rajouter un an à la date de dernière revalorisation. Pour calculer le nouveau prix, nous ajoutons l'ancien à la revalorisation. Vous n'avez plus qu'à transformer cette requête en requête Modification (Update) pour avoir une revalorisation automatisée.

Il n'y a aucune limite à la création des formules. Vous devez simplement respecter les règles des opérateurs et prioriser vos calculs par des parenthèses. Vous pouvez également utiliser des fonctions mathématiques ou non d'ACCESS, ou créer vos propres fonctions. Au travers de ce tutoriel, nous venons non seulement de repousser plus loin les limites d'ACCESS mais également de nous affranchir dans une moindre mesure de l'intervention d'EXCEL tout en laissant l'utilisateur maître de rédiger ses propres formules de calculs.

À Jeannot45 alias Jean BALLAT pour ses corrections et critiques toujours constructives ainsi qu'à toute l'équipe de Developpez.