RESEXE.REQRPT = " SELECT DISTINCT pays.nompay,COUNT(client.numctr) AS nbprospect" & _ " FROM pays,client" & _ " WHERE client.codpay = pays.codpay" & _ " AND flgprp = 1" & _ " AND MONTH(client.datcre) = " & RPT.MOI & _ " AND YEAR(client.datcre) = " & RPT.ANN & _ " GROUP BY pays.nompay" & _ " ORDER BY pays.nompay"
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionSub Recuperation() Dim ss_cmd As ADODB.Command Dim ss_parmoi As Parameter Dim ss_parann As Parameter Dim ss_rst As ADODB.Recordset Dim ss_tgtran As Range Dim ss_colind As Integer On Error GoTo SelectSQL_Error Connexion If FLGCNX = True Then RESEXE.X = 13 RESEXE.Y = 3 'On déclare la command Set ss_cmd = New ADODB.Command With ss_cmd .ActiveConnection = CNXFOX .CommandType = adCmdText .NamedParameters = True End With ss_cmd.Parameters.Append ss_cmd.CreateParameter("ChoixMois", adInteger, 2) ss_cmd.Parameters.Append ss_cmd.CreateParameter("ChoixAnnee", adInteger, 4) ss_cmd("ChoixMois").Value = RPT.MOI ss_cmd("ChoixAnnee").Value = RPT.ANN SelectionRequete ss_cmd.CommandText = "PARAMETERS ChoixMois Int, ChoixAnnee Int;" & RESEXE.REQRPT 'On déclare le recordset Set ss_rst = New ADODB.Recordset 'Exécute la requête With ss_rst .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockReadOnly End With Set ss_rst = ss_cmd.Execute 'On efface l'ensemble du contenu précédent With Workbooks(NOMFIC).Worksheets(FEUPRP) 'On vérifie que l'on a bien récupéré des enregistrements If Not ss_rst.EOF Then ss_rst.MoveFirst .Cells(RESEXE.X, RESEXE.Y).CopyFromRecordset ss_rst 'On récupère le nombre d'éléments traités : RESEXE.NBRRES = ss_rst.RecordCount End If End With ss_rst.Close Set ss_cmd = Nothing Set ss_rst = Nothing End If Deconnexion On Error GoTo 0 Exit Sub SelectSQL_Error: MsgBox "(Erreur n°" & ERR.Number & ") " & ERR.Description RESEXE.ERR = ERR.Description FLGERR = True End Sub
Public Sub SelectionRequeteProspect() Select Case RPT.PER Case RPTMEN RESEXE.REQRPT = " SELECT DISTINCT pays.nompay,COUNT(client.numctr) AS nbprospect" & _ " FROM pays,client" & _ " WHERE client.codpay = pays.codpay" & _ " AND flgprp = 1" & _ " AND MONTH(client.datcre) = [ChoixMois]" & _ " AND YEAR(client.datcre) = [ChoixAnnee]" & _ " GROUP BY pays.nompay" & _ " ORDER BY pays.nompay" Case else MsgBox "On ne fait rien" End Select End Sub