Expression to complex

[Compufreak]

Hi everyone

I am getting the follwing error and i am not even sure why. All i want is for the full Month name to appear from a start date and end date. Please note that all this information is going to be displayed in a graph.

SELECT Department.Dept_Desc, Count(Month([Day_Month_Year])) AS Expr1, Source.Analysis
FROM (Department INNER JOIN (Clusters INNER JOIN Cluster_Dept ON Clusters.Cluster_ID = Cluster_Dept.Cluster_ID) ON Department.Dept_ID = Cluster_Dept.Dept_ID) INNER JOIN Source ON Cluster_Dept.ID = Source.ID
GROUP BY Department.Dept_Desc, Source.Analysis
HAVING (((Count(Month([Day_Month_Year]))) Between [Forms]![ViewReportsForm]![StartDate] And [Forms]![ViewReportsForm]![EndDate]));

that's my code , help? anyone?

[recyan]

Till some one come along :below is a bit puzzling,

HAVING (((Count(Month([Day_Month_Year]))) Between [Forms]![ViewReportsForm]![StartDate] And [Forms]!

COUNT() BETWEEN STARTDATE AND ENDDATE.What is the value being generated by COUNT() and what are the values for filtering being passed by the form?Thanks

[Compufreak]

hi i have sorted that query out. instead of displaying the date range on the graph , i created a unbound textbox and it displays the date range there. also i am counting the number of headlines per cluster and unit.

[recyan]

Glad you got things working.

Thanks

[Compufreak]

thanks i really appreciate it , i am still getting used to this whole access thing. if you have time , i am doing this query and i get a type mismatch error

Private Sub Command43_Click()
Dim strSQL As String
'namepo = [Combo1].Value
strSQL = "SELECT Department.Dept_Desc, Count(Source.Headline) AS NumberOfArticles, Source.Analysis " & _
         "FROM (Clusters INNER JOIN (Department INNER JOIN Cluster_Dept ON Department.Dept_ID=Cluster_Dept.Dept_ID)" & _
         "ON Clusters.Cluster_ID=Cluster_Dept.Cluster_ID) INNER JOIN Source ON Cluster_Dept.ID=Source.ID " & _
         "WHERE (((Source.Analysis)=Forms!ViewReportsForm!Combo53) And ((Source.Day_Month_Year) Between Forms!ViewReportsForm!StartDate And Forms!ViewReportsForm!EndDate) And ((Clusters.Cluster_Desc)=Forms!ViewReportsForm!Combo1)) " & _
         "GROUP BY Department.Dept_Desc, Source.Analysis"
         
         If IsNull(Combo1.Value) And IsNull([StartDate]) And IsNull([EndDate]) And IsNull([Search]) And IsNull([Combo53]) Then
            'run report without any condition
           strSQL = strSQL & ";"
         Else
                   If (Combo1.Value <> " ") And (([StartDate] <> " ") Or ([EndDate] <> " ")) And ([Combo53] <> " ") Then
                    strSQL = strSQL & " " & "WHERE Source.Day_Month_Year Between #" & [StartDate] & "# AND #" & [EndDate] & "# AND [Source].Analysis  =" & Chr(34) & [Combo53] &  Chr(34) And "&_"
                    [Clusters].Cluster_Desc = " & Chr(34) & [Combo1] & Chr(34) "
                    parSelection = [Combo1] & ";" & "Day_Month_Year:" & [StartDate] & "-" & [EndDate] & [Combo53] & ";"
                   End If
    End If
    
'   Assign SQL code to query that the entry form uses
             CurrentDb.QueryDefs("Query1").SQL = strSQL
             
            'Open the report
            DoCmd.OpenReport "Report1", acViewReport
End Sub

thanks again

[recyan]

Am not in to VBA, but till some one comes along, a few thoughts :

1)
strsql =
"SELECT
.......
FROM
....
WHERE
...
GROUP BY
...."
"
Now, we have an IF IsNull() condition, Then we want to run the query without any condition.
But we already have the WHERE clause before the IF.

2) Again, was wondering about the use of ELSE IF, since to me there appears to be only one
IF ISNULL()..... THEN
.............
ELSE ( here the NOT NULL )
.............
END IF

3) The operation that will take place, if the ELSE condition is executed,
strsql =
"SELECT
.......
FROM
....
WHERE
...
GROUP BY
...."
WHERE
........
"

4) Add a debug.Print strsql perhaps, just after the last End If & see what sql is getting printed in the IMMEDIATE window.
That should perhaps give you a clue.

Thanks