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

0
votes

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

2
sqlms-accessvba
What are your Variant fields in the database? That will define how you should handle them. – JoelC
When I say it doesn't work, it doesn't get an error but the parameters are not entered. So for instance the user will enter a company, a project and an employee and the report will generate just fine on the subreport, but when I hit the print button that is tied to the DoCmd.OpenReport done of that information is transferred so I am trying to include it in the where clause but as of yet I can't get the syntax correct – user3802731
also company, project, and employee will all be integers. Startdate and Enddate are of course date fields. If that helps. Again I'm not sure how access works behind scenes so I don't know if I define them as integers or something else – user3802731
I think I should explain better exactly how I have this set up. And please excuse my ignorance I'm sure this is not a "good practice" way of doing things but giving my limited knowledge this is how I've made it work. The subreport that the user generates is based off a query where I set parameters for EmployeeID, ProjectID, and CompanyID equal to cboEmployee, cboProject, and cboCompany. This allows the report to generate in the subreport no problem but the parameter values are not transfering to the print preview of the report. Thats why I want to include them in the where clause for OpenRepor – user3802731

2 Answers

1
votes

I think you may be using the wrong field names in the filter.

Suppose that in Report Design your report's RecordSource is tblProject.

When you do this:

  DoCmd.OpenReport "ProjectReport_Employee_R", , , "[cboCompany]= " & Company ...

you are effectively opening the report with a recordsource of:

  SELECT * FROM tblProject WHERE [cboCompany] = 12345 AND ...

If tblProject doesn't have a field named "cboCompany" then it won't work.

0
votes

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.