Hello everyone.
I am trying to visualize data in a graph with MS access. The data that i am trying to visualize is hourly data from diffirent sensors.
To do this I made a Query that comines all the data that needs to be on the graph. My idea was to make a Form where the user can chose what data to show (so which sensor they want to see) and I also need an option here where the user can chose to see the data hourly, monthly of yearly. I am trying to do this by changing the SQL code. so if i want to see the data hourly i need this:
Code:
SELECT (Format([Datum],"ddddd hh")) AS Expr1, Avg(Vermogen.PS_Buggenhout_Vermogen) AS GemVanPS_Buggenhout_Vermogen, Avg(Vermogen.PS_Hautrage_Vermogen) AS GemVanPS_Hautrage_Vermogen, Avg(Vermogen.PS_Mainvault_Vermogen) AS GemVanPS_Mainvault_Vermogen, Avg(Vermogen.PS_BrakelKerkhofstrt_Vermogen) AS GemVanPS_BrakelKerkhofstrt_VermogenFROM Vermogen
WHERE (((Vermogen.Datum) Between [Formulieren]![Grafiek_menu]![Datum1] And [Formulieren]![Grafiek_menu]![Datum2]))
GROUP BY (Format([Datum],"ddddd hh")), (Int([Datum]));
But when i try to run this code in VBA like this: (and fyi 'Vermogen' is the name of the query that i use to visualize the data)
Code:
Private Sub btn_Grafiek_Click() Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim SQLstring As String
Set qdf = db.QueryDefs("Gegevens Grafiek")
SQLstring = "SELECT Format([Datum],"ddddd hh") AS Expr1, Avg(Vermogen.[PS_BrakelKerkhofstrt_Vermogen]) AS GemVanPS_BrakelKerkhofstrt_Vermogen, Avg(Vermogen.[PS_Buggenhout_Vermogen]) AS GemVanPS_Buggenhout_Vermogen, Avg(Vermogen.[PS_Hautrage_Vermogen]) AS GemVanPS_Hautrage_Vermogen, Avg(Vermogen.[PS_Mainvault_Vermogen]) AS GemVanPS_Mainvault_Vermogen FROM Vermogen WHERE (((Vermogen.[Datum]) Between [Formulieren]![Grafiek_menu]![Datum1] And [Formulieren]![Grafiek_menu]![Datum2])) GROUP BY (Format([Datum],"ddddd hh")), (Int([Datum]));"
qdf.SQL = SQLstring
DoCmd.OpenForm "Grafiek"
End Sub
It keeps giving me an erro that i cant use "Format([Datum],"ddddd hh")". But in what other way can i put in the SQL code that i need to filter that data.
Thanks in advance!