Error while creating a chart with SQL

[alecitacruz]

Hello guys!!! I am new here and I am trying to create a chart resulting from a filter created in VBA, in which I first filter data with an SQL statement (Select...) and then I make a filter of time (something like: AND [Beginn] >=" + Date_exact_US_SQL(txtVon) + " AND [Beginn] <=" + Date_exact_US_SQL(LDate)...
The chart is created when I call it for one value, I use:
strSQL = strSQL + " SELECT '" + CStr(cboPST.Value) + "' AS Typ and then the date filter
And the chart plots cbo.PST AS Typ. So, it works.

THE PROBLEM

When I do the same, but for:
strSQLpst = "SELECT DISTINCT [PST] AS Typ
It plots the PST for all the dates given in the query it is based on.
However, when I introduce the date filter I get the following message:
An error occurred while sending data to the OLE server. You may have tried to send too much data.

The solution from the Help is to modify the query. I have tried that, but so far I have not come to a solution. Please, I need help!!! I am a beginner and I do not know how else to solve this!!

[cyberdwarf]

When I do the same, but for:
strSQLpst = "SELECT DISTINCT [PST] AS Typ
It plots the PST for all the dates given in the query it is based on.
However, when I introduce the date filter I get the following message:
An error occurred while sending data to the OLE server. You may have tried to send too much data.

Can you post the complete SQL statement please

Steve

[alecitacruz]

Yes, thank you!!! The only difference between the code that works and the one that doesn´t is that on the first one I use only one string, and this value comes from a combo box. In the one that doesn´t work I use a "SELECT DISTINCT" statement (to have distinct values coming from a table) .In both codes the data filter works, but the problem is with the date filter in the second one.

THE CODE THAT WORKS IS THE FOLLOWING:

strSQL = strSQL + " SELECT '" + CStr(cboPST.Value) + "' AS Typ, Sum(([Ende]-[Beginn])*24*60\60) AS Maxi, Avg(([Ende]-[Beginn])*24*60\60) AS Mittel "
strSQL = strSQL + " FROM Motoren WHERE 1=1 "

'DATE FILTER

strSQL = strSQL + " AND [Beginn] >=" + Date_exact_US_SQL(txtVon) + " AND [Beginn] <=" + Date_exact_US_SQL(LDate)

'FOR THE CHART:

strSQL_Dia = "SELECT [Typ],Sum([Maxi]) AS [Laufzeit], Sum([Mittel]) AS [Durchschnitt] FROM (" + strSQL + ") GROUP BY [Typ]"

And works!!!


THE CODE THAT DOESN´T WORK IS:


strSQLabt = "SELECT DISTINCT [Abteilung] AS Typ, Sum(([Ende]-[Beginn])*24*60\60) AS Maxi, Avg(([Ende]-[Beginn])*24*60\60) AS Mittel FROM Motoren WHERE [PST] = '" & Me.cboPST.Value & "' GROUP BY [Abteilung]"

Until here, it does work; it plots all the distinct "Abteilung", but for all the dates coming from the table...when I introduce the code for the date filter:

strSQLabt = strSQLabt & " AND [Beginn] >=" & Date_exact_US_SQL(txtVon) & " AND [Beginn] <=" & Date_exact_US_SQL(LDate)

The data goes through this (no error message) but for the chart:

strSQL_Dia = "SELECT [Typ],Sum([Maxi]) AS [Laufzeit], Sum([Mittel]) AS [Durchschnitt] FROM (" + strSQL + ") GROUP BY [Typ]"

The error says that Typ is not part of the function...and that i am sending too much data. Any ideas?