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!!

to concatenate strings use & and not +

Hey, thanks for clearing that out for me. However, it is still not working, even changing the + for &. 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. 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)

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...but 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, 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