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?
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?