type mismatch

[Compufreak]

so i am stumped on this one , i get a type mismatch error:



here is my code :

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

the part thats bold and underlined is were i am getting the error

[June7]

Try:
" WHERE Source.Day_Month_Year BETWEEN #" & [StartDate] & "# AND #" & [EndDate] & _
"# AND [Source].Analysis='" & [Combo53] & "' AND [Clusters].Cluster_Desc='" & [Combo1] & "'"

Assumes Day_Month_Year is a date/time datatype and the other fields are text type.

[Compufreak]

sure i will give it a try now

[Compufreak]

                    strSQL = strSQL & " " & " WHERE Source.Day_Month_Year BETWEEN #" & [StartDate] & "# AND #" & [EndDate] & _
                            "# AND [Clusters].Cluster_Desc='" & [Combo1] & "'" And "[Source].Analysis='" & [Combo53] & "'"

still getting the error

[orange]

Put in a debug.print strSQL to see what is being used.

[June7]

You have not used the exact structure I suggested. Look at the 'And' in front of Analysis. There are two extra quote marks.