Forum Discussion
Endless query
Maybe I'm missing something, but why checking for code_dossier in the stats-table from the subquery while code_dossier itself already comes from the stats table (in the outer query)?
WoldmanBecause it's a dynamically constructed query and in case of "all" selected the easier way to translate it it's by an "in" clause. Some case => (code_dossier in ('EC', 'EI', etc...) or all cases => in (select ...). The "code_dossier" are selected in a multiple list... The distinct return about 250 values and the full stats table have about 210 000 rows).
I have a case condition with 16 différents values (in my vba code) and i create a dynamic query that work fine until this case. I have to double the number of cases (for cumulative stats) so i used the already working method to add the new cases...
Made from multi list choice =>
so maybe : strDossier = "Select code_dossier from stats"
or : strDossier = "'EI','EC','TRUC'"
Different tests do the other parts of the query
strReqDeb = "SELECT Format([Jour],'YYYY-MM') as Grn"
strReq = ", sum([nb_collab]) as Som FROM [Stats] "
strReq = strReq & "WHERE [jour] >= [Formulaires]![F_Connexions]![txtDtDebut] and [jour] <= [Formulaires]![F_Connexions]![txtDtFin] and [nb_collab]<>0"
strReq = strReq & " and [code_dossier] in (" & strDossier & ")"
strReqFin = " group by Format([Jour],'YYYY-MM') ORDER BY 1;"And then i make the final query by jooining all parts
Me.graphic.RowSource = strReqDeb & strReq & strReqFinAnd this sample query is perfectly working and immediate
- I had to make like that because the rowsource size of a chart is limited to 2048 and if i make the "in clause" with all code_dossier, it's over (about 2500 chars)
