Trouble with DoCmd.OpenReport with a complicated Where Clause (Syntax Issue)

Question

So first I have gone over a very large amount of other sources trying to look for a solution or an explanation of my error, but I haven't found anything that helps me quite yet.

I have been learning all this stuff on my own so I am definitely lacking in some of the foundation knowledge and it has caught up to me. Basically the form I am having trouble with allows the user to generate three different reports based on the kind of criteria they care about. There are three different combo boxes cboCompany, cboProject, cboEmployee. I use the Nz command to change null values to zero and then use If Statements to determine which report to use depending on what the user selects. I hope that kind of makes since. Its easier to show than explain. That part all works fine though.

The problem I'm getting is when I want to do a print preview for the report the user generates. I need the where clause to take the values the user selects to use in the print preview. I can't get the syntax right for the where clause to actually do this.

Here is how I call my variables. The Nz function as I understand require the variables to be variants, because of this I'm not quite sure how to write the syntax for the where clause. I know integers, strings, and dates all have different syntax but I don't know what variant would be.

Dim Company As Variant
Dim Project As Variant
Dim Employee As Variant
Dim EndDate As String
Dim StartDate As String

Company = Nz(cboCompany, 0)
Project = Nz(cboProject, 0)
Employee = Nz(cboEmployee, 0)
StartDate = txtStartDate.Value
EndDate = txtEndDate.Value

So this next part is how I have written one of the report options with my best guess of the syntax for the where clause. It doesn't work so If you could help point me in the right direction I would greatly appreciate it.

 ElseIf Company <> 0 And Project <> 0 And Employee <> 0 Then
    DoCmd.OpenReport "ProjectReport_Employee_R", acViewPreview, "", "[cboCompany]= " & Company & " And [cboEmployee]= " & Employee & " And [cboProject]= " & Project & " And [txtStartDate]= #" & StartDate & "# And [txtEndDate]= #" & EndDate & "#", acWindowNormal

If I don't explain things well enough or you need more code to help please let me know, and thank you in advance.

***Edit: Just to give a more complete picture this is the code behind the entire form.

Private Sub cboCompany_AfterUpdate()
    Me.cboProject.Requery
End Sub

Private Sub cmdSubmit_Click()
    
    'Open Report With Filter Criteria
    Dim Company As Variant
    Dim Project As Variant
    Dim Employee As Variant
    
    Company = Nz(cboCompany, 0)
    Project = Nz(cboProject, 0)
    Employee = Nz(cboEmployee, 0)
    
    If Company = 0 Then
        MsgBox ("Please Select At Least A Company To Generate A Report")
        cboCompany.Value = Null
        cboProject.Value = Null
        cboEmployee.Value = Null
        
    ElseIf Company <> 0 And Project = 0 And Employee = 0 Then
        DoCmd.BrowseTo acBrowseToReport, "ProjectReport_Company_R", "Main_F.NavigationSubform>ProjectReport_F.Child31"
    
    ElseIf Company <> 0 And Project <> 0 And Employee = 0 Then
        DoCmd.BrowseTo acBrowseToReport, "ProjectReport_Project_R", "Main_F.NavigationSubform>ProjectReport_F.Child31"
    
    ElseIf Company <> 0 And Project <> 0 And Employee <> 0 Then
        DoCmd.BrowseTo acBrowseToReport, "ProjectReport_Employee_R", "Main_F.NavigationSubform>ProjectReport_F.Child31"
    
    ElseIf Company <> 0 And Project = 0 And Employee <> 0 Then
        MsgBox ("Please Select A Project")
        
    End If
    
End Sub

Private Sub Form_Current()
 Me.cboCompany.SetFocus
End Sub

Private Sub Form_Load()
   
'Set Date Values
   Dim EndDate As String
   Dim StartDate As String
        StartDate = Format(DateSerial(Year(Date), Month(DateAdd("m", -1, Date)), 16), "Short Date")
        EndDate = Format(DateSerial(Year(Date), Month(Date), 15), "Short Date")
    
    txtStartDate.Value = StartDate
    txtEndDate.Value = EndDate

'Set The Form Message on Open Child31
    DoCmd.BrowseTo acBrowseToForm, "ProjectReport_Message_F", "Main_F.NavigationSubform>ProjectReport_F.Child31"
    
   
    
End Sub
'------------------------------------------------------------
' Print_Click
'
'------------------------------------------------------------
Private Sub Command39_Click()
On Error GoTo Command39_Click_Err

    Dim Company As Variant
    Dim Project As Variant
    Dim Employee As Variant
    Dim EndDate As String
    Dim StartDate As String
    
    Company = Nz(cboCompany, 0)
    Project = Nz(cboProject, 0)
    Employee = Nz(cboEmployee, 0)
    StartDate = txtStartDate.Value
    EndDate = txtEndDate.Value
    
    
    If Company <> 0 And Project = 0 And Employee = 0 Then
    
        DoCmd.OpenReport "ProjectReport_Company_R", acViewPreview, "", "", acNormal
        
    ElseIf Company <> 0 And Project <> 0 And Employee = 0 Then
    
        DoCmd.OpenReport "ProjectReport_Project_R", acViewPreview, "", "", acNormal
        
    ElseIf Company <> 0 And Project <> 0 And Employee <> 0 Then
    
        DoCmd.OpenReport "ProjectReport_Employee_R", acViewPreview, "", "[cboCompany]= " & Company & " And [cboEmployee]= " & Employee & " And [cboProject]= " & Project & " And [txtStartDate]= '" & StartDate & "' And [txtEndDate]= '" & EndDate & "'", acWindowNormal
    
    End If


Command39_Click_Exit:
    Exit Sub

Command39_Click_Err:
    MsgBox Error$
    Resume Command39_Click_Exit

End Sub

Answer 1

I would base the report on a query rather than using the DoCmd's "WhereCondition" argument.

Make the "ProjectReport_Employee_R" recordsource a query called "qryProjectReport_Employee_R".

...then modify the query definition every time you want to run the report.

For example:

sub print_report()

'add these dim qdf as Dao.Querydef dim strSQL as String

if this then

ElseIf Company <> 0 And Project <> 0 And Employee <> 0 Then

'SQL may need modifying...
strSQL= "SELECT * FROM SomeTable WHERE [cboCompany]= " & Company & " And [cboEmployee]= " & Employee & " And [cboProject]= " & Project & " And [txtStartDate]= #" & StartDate & "# And [txtEndDate]= #" & EndDate & "#"

Set qdf="qryProjectReport_Employee_R"
qdf.SQL=strSQL

DoCmd.OpenReport "ProjectReport_Employee_R", acViewPreview

'clean up
set qdf=nothing

end if end sub

That's all. Let me know if you have any questions.