Microsoft 365 First and Last en ADODB VBA

VBA_dev_Anne_Marie

XLDnaute Occasionnel
Bonjour,

Je suis en train de coder en sql ADODB VBA. Je cherche à récupérer l'observation la plus récente (triée par date).
Pourriez-vous me donner l'exemple de la fonction First/Last en ADODB VBA ?

Merci beaucoup !
 

fanch55

XLDnaute Barbatruc
Vraiment très strict : un select doit finir par un ;
et il y en a 2 ...
VB:
Sub testMP()
Dim Requete As String
    db_Ev = "db_evenement"
    db_Cl = "dp_classe_evt"
    no_police = "T342GB00827"
    Requete = _
        " Select  max(d_effet),sum(Abs(mt_brut_cie)) " & _
        "           from " & db_Ev & " as T1 " & _
        "     Inner join " & db_Cl & " as T2 " & _
        "     On T1.is_classe_evt = T2.is_classe_evt" & _
        "     where T1.no_police = '" & no_police & "' and T2.b_ea =1 and T2.b_rachat = 1 " & _
        "   and T1.d_effet = ( " & _
        "    select Max(d_effet) " & _
        "     from       " & db_Ev & "  ev " & _
        "     Inner join " & db_Cl & " cl " & _
        "     On ev.is_classe_evt=cl.is_classe_evt" & _
        "     where ev.no_police = '" & no_police & "' and cl.b_ea =1 and cl.b_rachat = 1 " & _
        "    ;); "
    RECSET.Open Requete, cnn_Pegase, adOpenDynamic, adLockBatchOptimistic

End Sub
 

VBA_dev_Anne_Marie

XLDnaute Occasionnel
Vraiment très strict : un select doit finir par un ;
et il y en a 2 ...
VB:
Sub testMP()
Dim Requete As String
    db_Ev = "db_evenement"
    db_Cl = "dp_classe_evt"
    no_police = "T342GB00827"
    Requete = _
        " Select  max(d_effet),sum(Abs(mt_brut_cie)) " & _
        "           from " & db_Ev & " as T1 " & _
        "     Inner join " & db_Cl & " as T2 " & _
        "     On T1.is_classe_evt = T2.is_classe_evt" & _
        "     where T1.no_police = '" & no_police & "' and T2.b_ea =1 and T2.b_rachat = 1 " & _
        "   and T1.d_effet = ( " & _
        "    select Max(d_effet) " & _
        "     from       " & db_Ev & "  ev " & _
        "     Inner join " & db_Cl & " cl " & _
        "     On ev.is_classe_evt=cl.is_classe_evt" & _
        "     where ev.no_police = '" & no_police & "' and cl.b_ea =1 and cl.b_rachat = 1 " & _
        "    ;); "
    RECSET.Open Requete, cnn_Pegase, adOpenDynamic, adLockBatchOptimistic

End Sub
J'ai rempla
Vraiment très strict : un select doit finir par un ;
et il y en a 2 ...
VB:
Sub testMP()
Dim Requete As String
    db_Ev = "db_evenement"
    db_Cl = "dp_classe_evt"
    no_police = "T342GB00827"
    Requete = _
        " Select  max(d_effet),sum(Abs(mt_brut_cie)) " & _
        "           from " & db_Ev & " as T1 " & _
        "     Inner join " & db_Cl & " as T2 " & _
        "     On T1.is_classe_evt = T2.is_classe_evt" & _
        "     where T1.no_police = '" & no_police & "' and T2.b_ea =1 and T2.b_rachat = 1 " & _
        "   and T1.d_effet = ( " & _
        "    select Max(d_effet) " & _
        "     from       " & db_Ev & "  ev " & _
        "     Inner join " & db_Cl & " cl " & _
        "     On ev.is_classe_evt=cl.is_classe_evt" & _
        "     where ev.no_police = '" & no_police & "' and cl.b_ea =1 and cl.b_rachat = 1 " & _
        "    ;); "
    RECSET.Open Requete, cnn_Pegase, adOpenDynamic, adLockBatchOptimistic

End Sub
Merci, mais , malheureusement ça ne marche pas :
 
Dernière édition:

fanch55

XLDnaute Barbatruc
Essayez cela :
VB:
        " Select  Max(d_effet),Sum(Abs(mt_brut_cie)) " & _
        "           from " & db_Ev & " T1 " & _
        "     Inner join " & db_Cl & " T2 " & _
        "     On T1.is_classe_evt = T2.is_classe_evt" & _
        "     where T1.no_police = '" & no_police & "' and T2.b_ea = 1 and T2.b_rachat = 1 " & _
        "    and T1.d_effet = ( " & _
        "       select Max(d_effet)" & _
        "           from       " & db_Ev & " ev " & _
        "           Inner join " & db_Cl & " cl " & _
        "           On ev.is_classe_evt = cl.is_classe_evt" & _
        "       where ev.no_police= '" & no_police & "' and cl.b_ea = 1 and cl.b_rachat = 1 " & _
        "     )"
 

VBA_dev_Anne_Marie

XLDnaute Occasionnel
Essayez cela :
VB:
        " Select  Max(d_effet),Sum(Abs(mt_brut_cie)) " & _
        "           from " & db_Ev & " T1 " & _
        "     Inner join " & db_Cl & " T2 " & _
        "     On T1.is_classe_evt = T2.is_classe_evt" & _
        "     where T1.no_police = '" & no_police & "' and T2.b_ea = 1 and T2.b_rachat = 1 " & _
        "    and T1.d_effet = ( " & _
        "       select Max(d_effet)" & _
        "           from       " & db_Ev & " ev " & _
        "           Inner join " & db_Cl & " cl " & _
        "           On ev.is_classe_evt = cl.is_classe_evt" & _
        "       where ev.no_police= '" & no_police & "' and cl.b_ea = 1 and cl.b_rachat = 1 " & _
        "     )"
Merci beaucoup, ça marche bien ! C'était quoi, l'erreur ?
 

Discussions similaires

Réponses
1
Affichages
612