[ADODB] Obtenir la somme des prix par date même quand le résultat est null

Bonjour le forum !

Alors je sais que le titre correspond pas trop à m'a demande mais c'est difficile d'expliquer dans le titre avec le nombre de caractère ce que je cherchais ducou je vais expliquer avec des schéma ça sera plus compréhensible. J'ai actuellement un fichier excel regroupant des achats d'articles par date, prix et je souhaite regrouper tous les articles par date (année) en fessant la somme des prix. Pour l'instant avec ce code, j'obtiens un tableau comme celui-ci :

Dim sql As String
Dim rs As New ADOR.Recordset
Dim arr As Variant
Const filepath As String = "C:\Users\XXXXXX\Documents\excel\XXXXXX.xlsm"

Dim connectionString As String
connectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=""" & filepath & """;" & _
    "Extended Properties=""Excel 12.0;HDR=Yes"""

First_Timer = Timer()
sql = "SELECT data.[article], FORMAT (data.[date], 'yyyy'), SUM(data.[price]) FROM [data$] as data GROUP BY data.[article], FORMAT (data.[date], 'yyyy') "

rs.Open sql, connectionString
arr = rs.GetRows
rs.Close

Par exemple avec ces données :

Avec mon code actuel j'obtiens ceci :

Mais je souhaiterais avoir :

En cherchant des solutions sur différents forums j'ai compris qu'on pouvait mettre un IIF ou un COALESCE sur un SUM pour que SQL renvoie 0 si aucune colonne avec la date n'existe comme ceci mais j'obtiens à chaque fois des erreurs :

SELECT data.[article], FORMAT (data.[date], 'yyyy'), IIF(SUM(data.[price]) IS NULL,0,SUM(data.[price])) FROM [data$] as data GROUP BY data.[article], FORMAT (data.[date], 'yyyy') 

SELECT data.[article], FORMAT (data.[date], 'yyyy'), SUM(IIF(data.[price] IS NULL,0,SUM(data.[price]))) FROM [data$] as data GROUP BY data.[article], FORMAT (data.[date], 'yyyy')

ERREUR : "Les arguments sont du mauvais type, hors de portée ou en conflit les uns avec les autres"

SELECT data.[article], FORMAT (data.[date], 'yyyy'), COALESCE(SUM(data.[price]),0) FROM [data$] as data GROUP BY data.[article], FORMAT (data.[date], 'yyyy')

ERREUR : "La methode 'Open' de l'objet '_Recordset' à échoué"

Si quelqu'un a une piste pour moi, je suis preneur :)

Bonjour,

En VBA, la bonne syntaxe d'un SUM-IIF est plutôt du genre : SUM(IIF(ISNULL(data.[price]),0,data.[price]))

Pierre

Bonsoir,

Ducou j'ai testé ta fonction

"SELECT article, year(date),SUM(IIF(ISNULL(price),0,price)) FROM [data$] as data group by article, year(date)"

Et elle marche mais me renvoie exactement la même chose que ma précédente requête , comme si j'avais fais :

"SELECT article, year(date),SUM(price) FROM [data$] as data group by article, year(date)"

Une idée de ce qui bloque ? ou peut être que la ligne SQL est pas bonne du tout ?

Bonjour,

Hors contexte, c'est difficile de répondre précisément. Ce que je peux proposer c'est de vous référer à un exemple concret et pleinement fonctionnel dans le fichier que j'ai posté ici récemment =>

https://forum.excel-pratique.com/excel/gestion-heures-et-presence-absence-mise-en-forme-conditionnel...

Il y a des SUM_IIF à plusieurs occasions (cf module Sql). Prendre la version "g" du fichier.

Pierre

PS : à vérifier dans votre fichier si certaines valeurs de "Price" ne sont pas au format texte. Et aussi si cette colonne a bien un intitulé unique. Enfin c'est toujours bien de préciser l'amplitude des données : FROM [data$A1:G] par exemple

Me revoila,

Ducou je suis aller voir ton programme et ça m'a bien aidé merci ! J'ai pas encore trouvé la formule SQL que je veux mais j'ai un bon début

    sql = "SELECT article,"

    For i = 2010 To 2021
        sql = sql & " SUM(IIF(year(date) = " & i & ", price, 0))"
        If (i <> 2021) Then sql = sql & ","
    Next

    sql = sql & " FROM (SELECT * FROM [data$] as data) group by article"

C'est presque ce que je veux, je sens que j'y suis presque, pour l'instant j'arrive pas à faire comprendre à SQL que je veux chaque article avec chaque année la somme sans que ça crash ou que ça me donne des résultats improbables.

Bonjour Frej, Pierre

@Frej vous tenez impérativement à passer par du VBA avec PowerQuery il est certainement possible de faire quelque chose

A+

Bonjour à tous,

En effet peut être avec PQ, mais perso je ne sais pas l'utiliser.

Sinon dans le code indiqué, le year(date) me gêne (mais peut être à tort?).

Pour moi date est un mot clé du Vba ET du Sql. Si un champ est nommé date, j'écrirais alors year(`date`) (entouré de guillemets-du-AltGr-7-2-fois)

(D'ailleurs, j'utilise systématiquement cette notation pour les noms des champs)

A tester ...

Pierre

Bonjour à tous

A la demande BrunoM45 je poste une solution PowerQuery

Si j'ai bien compris la source est un fichier Excel : on peut attaquer le fichier sans l'ouvrir ni le charger dans un onglet

Si c'est cela, je peux déplacer les données dans un second fichier et communique le code M modifié.

Pour simplifier la compréhension j'ai découpé en 2 requêtes mais on pourrait faire en une

On peut actualiser manuellement ou par une ligne de VBA

Bonjour,

je ne sais pas si ça apporte quelques chose mai bon!

Bonjour aux nouveaux contributeurs, et excusé moi pour cette réponse tardif,

Je suis aller voir pour PowerQuery (que je connaissais pas du tout) et en effet ç'a à l'air d'être un outil très puissant ! mais je pense me rabattre pour l'instant sur la proposition de @dysorthographie, qui a parfaitement compris ce que je cherchais, et voir après pour mettre PowerQuery quand j'aurai mieux compris ce que @78chris à fais et mieux compris comment marche PowerQuery en générale et voir qu'elles sont ses possibilités / limites.

Merci à vous tous pour votre aide. :)