Comment combiner 3 variable VBA en date de datteype dans VBA

'Dim two date variables Dim fromDate As Date Dim toDate As Date 'Dim 4 integer variables Dim fromyear As Integer Dim toyear As Integer Dim frommonth As Integer Dim ToMonth As Integer 'Store combo box value into these 4 integer variables fromyear = FromYearC.Value frommonth = FromMonthC.Value toyear = ToYearC.Value ToMonth = ToMonthC.Value 'Now i want to combine these integer and store into fromDate and toDate fromDate = WorksheetFunction.Date(fromyear, frommonth, 1) toDate = WorksheetFunction.Date(toyear, ToMonth, 31)

ERREUR: Objet ne supporte pas cette méthode

Est-il impossible de mettre la variable VBA dans la fonction excel?

mise à jour SQL

  sSQLSsortingng = "SELECT officer ,NULL, SUM(IIF( isnumeric(mkt) = true and Survey='CPI' and Activity='FI' and Outcome= 'C', Totalmin, 0 )/468) ," & _ " SUM(IIF( isnumeric(Non) = true and Survey='CPI' and Activity='FI' and Outcome= 'C', Totalmin, 0 )/468) ,NULL ,NULL , " & _ "IIF(ISNULL(sum(mkt)),0,sum(mkt)),Sum(Non),sum(ICP),(sum(mkt)+Sum(Non)+sum(ICP) ) ,NULL,NULL,NULL,count(IIF( Survey='CPI' and Activity='FI' ," & _ "Totalmin, NULL )),NULL,count(IIF( Survey='CPI' and Activity='FI' and (Outcome ='C' OR Outcome='D'OR Outcome='O') , Totalmin, NULL )),NULL,SUM(IIF( Survey='CPI' and Activity='FI' ,Totalmin, 0 )),NULL,SUM(IIF( Survey='CPI' and Activity='FI' and (Outcome ='C' OR Outcome='D') ,Totalmin, 0 )) From (select officer ,rank ,year ,month ,day , survey ,activity ,outcome ,mkt,non,totalmin,ICP ,date from [2014$] UNION ALL select officer ,rank ,year ,month ,day , survey ,activity ,outcome ,mkt,non,totalmin,ICP ,date from [2015$] UNION ALL select officer ,rank ,year ,month ,day , survey ,activity ,outcome ,mkt,non,totalmin,ICP ,date from [2016$] UNION ALL select officer ,rank ,year ,month ,day , survey ,activity ,outcome ,mkt,non,totalmin,ICP ,date from [2017$])as table3 " & _ "where officer is not null and officer <> '' and officer <> ' ' and date >= #" & fromDate & "# AND date<=#" & toDate & "# group by officer"

Voici sSQLSsortingng où fromDate = #6/1/2016# et toDate = #7/1/2016# :

 SELECT officer ,NULL, SUM(IIF( isnumeric(mkt) = true and Survey='CPI' and Activity='FI' and Outcome= 'C', Totalmin, 0 )/468) , SUM(IIF( isnumeric(Non) = true and Survey='CPI' and Activity='FI' and Outcome= 'C', Totalmin, 0 )/468) ,NULL ,NULL , IIF(ISNULL(sum(mkt)),0,sum(mkt)),Sum(Non),sum(ICP),(sum(mkt)+Sum(Non)+sum(ICP) ) ,NULL,NULL,NULL,count(IIF( Survey='CPI' and Activity='FI' ,Totalmin, NULL )),NULL,count(IIF( Survey='CPI' and Activity='FI' and (Outcome ='C' OR Outcome='D'OR Outcome='O') , Totalmin, NULL )),NULL,SUM(IIF( Survey='CPI' and Activity='FI' ,Totalmin, 0 )),NULL,SUM(IIF( Survey='CPI' and Activity='FI' and (Outcome ='C' OR Outcome='D') ,Totalmin, 0 )) From (select officer ,rank ,year ,month ,day , survey ,activity ,outcome ,mkt,non,totalmin,ICP ,date from [2014$] UNION ALL select officer ,rank ,year ,month ,day , survey ,activity ,outcome ,mkt,non,totalmin,ICP ,date from [2015$] UNION ALL select officer ,rank ,year ,month ,day , survey ,activity ,outcome ,mkt,non,totalmin,ICP ,date from [2016$] UNION ALL select officer ,rank ,year ,month ,day , survey ,activity ,outcome ,mkt,non,totalmin,ICP ,date from [2017$])as table3 where officer is not null and officer <> '' and officer <> ' ' and date >= #6/1/2016# AND date<=#7/1/2016# group by officer

Apparemment, la VBA ne rend pas la fonction WorksheetFunction.Date disponible pour l'utilisation.

Avec l'aide de Rory, nous avons établi le bon moyen d'atsortingbuer les valeurs de la date comme suit:

 fromDate = DateSerial(fromyear, frommonth, 1) toDate = DateSerial(toyear, ToMonth + 1, 0)

La manière appropriée d'utiliser des dates dans une string sql est de les entourer dans hashes #.

WHERE tabledate> = # "& fromDate &" # AND tabledate <= # "& toDate &" # "

Si tabledate est un champ DateTime, vous devrez modifier votre code pour refléter les dates <toDate + 1 lieu de <=toDate . De cette façon, vous obtiendrez les loggings, y compris toDate + TimeValue .

Voici les modifications de VBA DateTime

 fromDate = DateSerial(fromyear, frommonth, 1) toDate = DateSerial(toyear, ToMonth + 1, 1)

C'est la nouvelle string SQL

WHERE tabledate> = # "& fromDate &" # AND tabledate <# "& toDate &" # "

La meilleure façon de tester la requête est de Debug.Print . Debug.Print le dans la window immédiate. Ensuite, vous copyz la requête et l'exécutez dans la database réelle. De là, vous pouvez modifier le SQL. Enfin, vous devez modifier votre code VBA pour refléter les modifications apscopes au SQL.

N'utilisez pas WorksheetFunction lorsque vous pouvez utiliser des fonctions VBA natives à la place. La fonction DateSerial fait dans le code de la Date de Excel dans une feuille de calcul:

Change ça:

 fromDate = WorksheetFunction.Date(fromyear, frommonth, 1) toDate = WorksheetFunction.Date(toyear, ToMonth, 31)

À:

 fromDate = DateSerial(fromyear, frommonth, 1) toDate = DateSerial(toyear, ToMonth, 31)

Notez le code codé 31 ici, pas tous les mois ont 31 jours.

Utilisez la fonction de feuille de calcul EoMonth comme en fin de mois au lieu de coder en dur une date pouvant entraîner une erreur.

 Sub e(fromyear As Long, frommonth As Long, toyear As Long, tomonth As Long) Dim fromdate As Date, todate As Date fromdate = DateSerial(fromyear, frommonth, 1) todate = Application.WorksheetFunction.EoMonth(DateSerial(toyear, tomonth, 1), 0) MsgBox Format(fromdate, "yyyy-mm-dd") & " to " & Format(todate, "yyyy-mm-dd") End Sub Sub f() Call e(2011, 11, 2016, 2) End Sub