gabrenicolas
Messages postés9Date d'inscriptionlundi 3 août 2009StatutMembreDernière intervention 1 septembre 2009
-
1 sept. 2009 à 14:21
nhervagault
Messages postés6063Date d'inscriptiondimanche 13 avril 2003StatutMembreDernière intervention15 juillet 2011
-
1 sept. 2009 à 19:16
Bonjour à tous,
Bon j'ai un problème de date: en effet dans un formulaire, je calcul le nombre d'activité faite par jour dans une colonne A, dans la colonne B je dois insérer manuellement le temps de travail par activité jusque là no pb au dessus de tout ca apparait à l'ouverture la date du jour qui determine les totaux .
Le problème, lorsque je change la date la recherche ne s'effectue plus correctement ..Pour certaine date no pb pour d'autre j'ai un resultat nul alors qu'il y à des données...
Ci-joint un bout de code
Private Sub findata()
Set db = Application.CurrentDb
'load data for open 1 client
sqlQuery = "SELECT Count(*) AS CountOfTYPE " & _
"FROM (USERS INNER JOIN HISTORY ON USERS.ID HISTORY.USER_ID) INNER JOIN TYPE ON HISTORY.COUPONTYPE_ID TYPE.ID " & _
"WHERE (((TYPE.TYPE)='1 client') AND HISTORY.STATUS_ID=2 AND [USERS].[T_FirstName] & ' ' & [USERS].[T_LastName] = '" & userLoggedName & "' AND (Format([history].[modifydatetime],'DD/MM/YYYY'))=#" & [Forms]![Coupon_Activities_form]![txtDateRef] & "#) "
Set rsResult = db.OpenRecordset(sqlQuery)
Me.txtopen1Nbr = rsResult!CountOfTYPE
Set db = Application.CurrentDb
'load data for open 2 client
sqlQuery = "SELECT Count(*) AS CountOfTYPE " & _
"FROM (USERS INNER JOIN HISTORY ON USERS.ID HISTORY.USER_ID) INNER JOIN TYPE ON HISTORY.COUPONTYPE_ID TYPE.ID " & _
"WHERE (((TYPE.TYPE)='2 client') AND HISTORY.STATUS_ID=2 AND [USERS].[T_FirstName] & ' ' & [USERS].[T_LastName] = '" & userLoggedName & "' AND (Format([history].[modifydatetime],'DD/MM/YYYY'))=#" & [Forms]![Coupon_Activities_form]![txtDateRef] & "#) "
Set rsResult = db.OpenRecordset(sqlQuery)
Me.txtopen2Nbr = rsResult!CountOfTYPE
Set db = Application.CurrentDb
'load data for open HB/Required
sqlQuery = "SELECT Count(*) AS CountOfTYPE " & _
"FROM (USERS INNER JOIN HISTORY ON USERS.ID HISTORY.USER_ID) INNER JOIN TYPE ON HISTORY.COUPONTYPE_ID TYPE.ID " & _
"WHERE (((TYPE.TYPE)='HB/Required') AND HISTORY.STATUS_ID=2 AND [USERS].[T_FirstName] & ' ' & [USERS].[T_LastName] = '" & userLoggedName & "' AND (Format([history].[modifydatetime],'DD/MM/YYYY'))=#" & [Forms]![Coupon_Activities_form]![txtDateRef] & "#) "
Set rsResult = db.OpenRecordset(sqlQuery)
Me.txtopenhbNbr = rsResult!CountOfTYPE
Set db = Application.CurrentDb
'load data for open Non Client
sqlQuery = "SELECT Count(*) AS CountOfTYPE " & _
"FROM (USERS INNER JOIN HISTORY ON USERS.ID HISTORY.USER_ID) INNER JOIN TYPE ON HISTORY.COUPONTYPE_ID TYPE.ID " & _
"WHERE (((TYPE.TYPE)='Non Client') AND HISTORY.STATUS_ID=2 AND [USERS].[T_FirstName] & ' ' & [USERS].[T_LastName] = '" & userLoggedName & "' AND (Format([history].[modifydatetime],'DD/MM/YYYY'))=#" & [Forms]![Coupon_Activities_form]![txtDateRef] & "#) "
Set rsResult = db.OpenRecordset(sqlQuery)
Me.txtopenncNbr = rsResult!CountOfTYPE
Set db = Application.CurrentDb
'load data for open Mail out
sqlQuery = "SELECT Count(*) AS CountOfTYPE " & _
"FROM (USERS INNER JOIN HISTORY ON USERS.ID HISTORY.USER_ID) INNER JOIN TYPE ON HISTORY.COUPONTYPE_ID TYPE.ID " & _
"WHERE ((HISTORY.STATUS_ID=3 or HISTORY.STATUS_ID=5 or HISTORY.STATUS_ID=6) AND [USERS].[T_FirstName] & ' ' & [USERS].[T_LastName] = '" & userLoggedName & "' AND (Format([history].[modifydatetime],'DD/MM/YYYY'))=#" & [Forms]![Coupon_Activities_form]![txtDateRef] & "#) "
Set rsResult = db.OpenRecordset(sqlQuery)
Me.txtMailoutNbr = rsResult!CountOfTYPE
Set db = Application.CurrentDb
'load data for open cleaning
sqlQuery = "SELECT Count(*) AS CountOfTYPE " & _
"FROM (USERS INNER JOIN HISTORY ON USERS.ID HISTORY.USER_ID) INNER JOIN TYPE ON HISTORY.COUPONTYPE_ID TYPE.ID " & _
"WHERE ((HISTORY.STATUS_ID=1 or HISTORY.STATUS_ID=17 or HISTORY.STATUS_ID=18 or HISTORY.STATUS_ID=20) AND [USERS].[T_FirstName] & ' ' & [USERS].[T_LastName] = '" & userLoggedName & "' AND (Format([history].[modifydatetime],'DD/MM/YYYY'))=#" & [Forms]![Coupon_Activities_form]![txtDateRef] & "#) "
Set rsResult = db.OpenRecordset(sqlQuery)
Me.txtcleaningNbr = rsResult!CountOfTYPE
rsResult.Close
If (Me.txtopen1Nbr.value + Me.txtopen2Nbr.value + Me.txtopenhbNbr.value + Me.txtopenncNbr.value + Me.txtMailoutNbr.value + Me.txtcleaningNbr.value) = 0 Then
cmdAdd.Visible = False
MsgBox "No Activities encoded for this date"
Else: cmdAdd.Visible = True
End If
End Sub
Merci pour tout forme de reponses ou éclaircicement sur les formatages de dates ou autres
