filtering reports from code

CHAOSinACT

Registered User.
Local time
Tomorrow, 08:42
Joined
Mar 18, 2009
Messages
235
ok i have a sub that is designed to get data from a form and filter a report from the user choice. i need to the docmd object to accept multiple where clause which are built from vba. NOW i know there are some fields with spaces (bad bad! me) but this thing evolved over a long time and i was stupid at the start! so. in this sitch it tells me i'm wrong:

Private Sub btnCivilGlobalByNumberReport_Click()
Dim strSQLWhereClause As String



If Form_frmCivilGlobalReports.tglEditCivilReportsByNumber.Value = 0 Then
'send generic report
DoCmd.OpenReport "rptCivilGlobalReportsByNumber", acViewPreview

Else
'filter branching construct
'quote only

If Form_frmCivilGlobalReports.chkCivilReportQuoteOnly.Value = True Then
strSQLWhereClause = "[Quote Only] = True"
Else
strSQLWhereClause = "[Quote Only] = False"
End If

'Finished

If Form_frmCivilGlobalReports.chkCivilReportFinished.Value = True Then
strSQLWhereClause = strSQLWhereClause & " And Finished = True"
Else
strSQLWhereClause = strSQLWhereClause & " And Finished = False"
End If

'Foreman
If Form_frmCivilGlobalReports.cmbCivilReportsForeman <> "" Then
strSQLWhereClause = strSQLWhereClause & " And ProjectForeman = " & Form_frmCivilGlobalReports.cmbCivilReportsForeman
End If

'suburb
If Form_frmCivilGlobalReports.txtCivilReportsSuburb.Value <> "" Then
strSQLWhereClause = strSQLWhereClause & " And Suburb = " & Form_frmCivilGlobalReports.txtCivilReportsSuburb.Value
End If
'scale
If Form_frmCivilGlobalReports.cmbCivilReportsScale.Value <> "" Then
strSQLWhereClause = strSQLWhereClause & " And [Project Scale] = " & Form_frmCivilGlobalReports.cmbCivilReportsScale.Value
End If
'status
If Form_frmCivilGlobalReports.cmbCivilReportsStatus.Value <> "" Then
strSQLWhereClause = strSQLWhereClause & " And ProjectStatus = " & Form_frmCivilGlobalReports.cmbCivilReportsStatus.Value
End If
DoCmd.OpenReport "rptCivilGlobalReportsByNumber", acViewPreview, strSQLWhereClause

End If
End Sub

i know it fails with the first thing (well at docmd but the first quote only is a prob) don't brackets solve this?
 
well the first 2 toggle controls so true/false really.
 
Well, add this right before the OpenReport and you can examine the final result in the Immediate window:

Debug.Print strSQLWhereClause

If you don't spot the problem, post it here. Oh and I think you're missing a comma. Try this:

DoCmd.OpenReport "rptCivilGlobalReportsByNumber", acViewPreview, , strSQLWhereClause
 

Users who are viewing this thread

Back
Top Bottom