RESEXE.REQRPT = "SELECT DISTINCT pays.nompay,COUNT(client.numctr) AS nbrprp" & _ " FROM pays,client" & _ " WHERE client.codpay = pays.codpay" & _ " AND client.flgprp = 1" & _ " AND client.datcre BETWEEN {^" & DEBPER & "}" & _ " AND {^" & FINPER & "}" & _ " GROUP BY pays.nompay" & _ " ORDER BY pays.nompay"
DEBPER = "2010-11-18"
DEBPER = "2010/11/18"
Sub RecuperationProspect() Dim rp_cmd As ADODB.Command Dim rp_debper As ADODB.Parameter Dim rp_finper As ADODB.Parameter Dim rp_rst As ADODB.Recordset On Error GoTo RecuperationProspect_Error 'Procédure de connexion aux tables foxpro Connexion 'Si le flag me renvoi true, la connexion est ouverte donc.. If FLGCNX = True Then Set rp_cmd = New ADODB.Command With rp_cmd .ActiveConnection = CNXFOX .CommandType = adCmdText .NamedParameters = True End With 'Mes deux paramètres Set rp_debper = New ADODB.Parameter Set rp_finper = New ADODB.Parameter 'Leurs propriétés With rp_debper .Direction = adParamInputOutput .Type = adBSTR .Name = "DebutPeriode" End With With rp_finper .Direction = adParamInputOutput .Type = adBSTR .Name = "FinPeriode" End With 'On les ajoute à la collection de paramètre de l'objet Command rp_cmd.Parameters.Append rp_debper rp_cmd.Parameters.Append rp_finper 'On donne les valeurs, le début et la fin de période(type string) rp_debper.Value = DEBPER rp_finper.Value = FINPER 'Procédure qui récupère la requete en y passant les paramètres 'je la donne plus bas RecuperationRequeteProspect rp_debper, rp_finper rp_cmd.CommandText = RESEXE.REQRPT Set rp_rst = New ADODB.Recordset With rp_rst .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockReadOnly End With rp_rst.Open rp_cmd 'Ensuite vous faites tous les traitements voulus avec votre recordset rp_rst.Close Set rp_cmd = Nothing Set rp_debper = Nothing Set rp_finper = Nothing Set rp_rst = Nothing End If 'Fonction pr se déconnecter des tables.. Deconnexion On Error GoTo 0 Exit Sub Recuperation_Error: MsgBox "(Erreur n°" & ERR.Number & ") " & ERR.Description End Sub
Public Sub RecuperationRequeteProspect(sr_debper As ADODB.Parameter, sr_finper As ADODB.Parameter) RESEXE.REQRPT = "SELECT DISTINCT pays.nompay,COUNT(client.numctr) AS nbrprp" & _ " FROM pays,client" & _ " WHERE client.codpay = pays.codpay" & _ " AND client.flgprp = 1" & _ " AND client.datcre BETWEEN {^" & sr_debper & "}" & _ " AND {^" & sr_finper & "}" & _ " GROUP BY pays.nompay" & _ " ORDER BY pays.nompay" End Sub