If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Specifying conditional Criteria in a Query or Report
Hi Guys and Gals,
I have a number of reports each fed by a different query. In each query I have criteria supplied by a form (for example, Between [forms]![frmDateRange]![txtDateFrom] And [forms]![frmDateRange]![txtDateTo]) which works just fine. However, now I want to add the option to specify additional criteria using the form such as an equipment number, operator etc. Naturally, each criteria is not being specified each time the report is being run and so I need to include certain criteria only when it is chosen in the form. So I used an IIf statement to try and make the criteria only apply when the combo box on the form is filled out (for example, IIf([forms]![frmDateRange]![cbxEquipment] Not IsNull, [forms]![frmDateRange]![cbxEquipment]) ) When the query is run from the form, if the combobox has something in it, it works fine, but if there is nothing in the combobox, the report displays nothing. If I remove the IIF statement from the criteria, the report displays all of the records regardless of which equipment it is (as you would expect). What am I doing wrong with my IIf statement? Is there a better way to do this especially considering some reports will have up to 5 criteria specified or not. Cheers Stephen @ ZennHAUS |
#2
|
|||
|
|||
Specifying conditional Criteria in a Query or Report
Stephen @ ZennHAUS wrote:
Hi Guys and Gals, make the criteria only apply when the combo box on the form is filled out (for example, IIf([forms]![frmDateRange]![cbxEquipment] Not IsNull, [forms]![frmDateRange]![cbxEquipment]) ) An IIF statement has teh following format IIF(Something, true, false) If somethig evaluates is true then the true part evaluates, if not then the false side does. You have no "false" side. Access should throw an error but probably returns Null. You may have to build the query in code. This is not hard but can be frustrating and timeconsuming. TxtSQLF = "first part of code that is always the same. Watch the use of single and double quotes" TxtSQLB = " the rest of the code" If Me.[cbxEquipment] then TxtSQL = TxtSQLF & "Where cbxEquipment = '" & me.cbxequioment & "' " & TxtSQLB Else TxtSQL = TxtSQLF & TxtSQLB End If is a quick example. Then run Msgbox TxtSql until it looks right, watching for bad quotes, etc. Finally, when it is right you can run it from DoCmd. |
#3
|
|||
|
|||
Specifying conditional Criteria in a Query or Report
You need to buil a string variable based on what is selected
in the relevant form controls, then apply that string as a filter or use it as criteria in a where clause. You can find a working example of this at; http://allenbrowne.com/ser-62.html -- _________ Sean Bailey "Stephen @ ZennHAUS" wrote: Hi Guys and Gals, I have a number of reports each fed by a different query. In each query I have criteria supplied by a form (for example, Between [forms]![frmDateRange]![txtDateFrom] And [forms]![frmDateRange]![txtDateTo]) which works just fine. However, now I want to add the option to specify additional criteria using the form such as an equipment number, operator etc. Naturally, each criteria is not being specified each time the report is being run and so I need to include certain criteria only when it is chosen in the form. So I used an IIf statement to try and make the criteria only apply when the combo box on the form is filled out (for example, IIf([forms]![frmDateRange]![cbxEquipment] Not IsNull, [forms]![frmDateRange]![cbxEquipment]) ) When the query is run from the form, if the combobox has something in it, it works fine, but if there is nothing in the combobox, the report displays nothing. If I remove the IIF statement from the criteria, the report displays all of the records regardless of which equipment it is (as you would expect). What am I doing wrong with my IIf statement? Is there a better way to do this especially considering some reports will have up to 5 criteria specified or not. Cheers Stephen @ ZennHAUS |
#4
|
|||
|
|||
Specifying conditional Criteria in a Query or Report
Thanks for your suggestions Mike.
I am familiar with building queries in code and find it quite easy to do. What I don't know in this case is how do I pass the resulting dynaset to the report then. Also, for your own information, you are correct about the IIF statement syntax being IIF(condition, truepart, falsepart). However, if you leave the truepart blank or omit the falsepart completely, Access does nothing (or at least it is supposed to do nothing). In this case is seems to assume that because I have the IIf statement in the criteria there should be something there rather than opting for doing nothing. I have tried using a false part of Null, IsNull and "". But all of these have the same result as omitting the falsepart completely. :-( Cheers Stephen @ ZennHAUS "Mike Painter" wrote in message ... Stephen @ ZennHAUS wrote: Hi Guys and Gals, make the criteria only apply when the combo box on the form is filled out (for example, IIf([forms]![frmDateRange]![cbxEquipment] Not IsNull, [forms]![frmDateRange]![cbxEquipment]) ) An IIF statement has teh following format IIF(Something, true, false) If somethig evaluates is true then the true part evaluates, if not then the false side does. You have no "false" side. Access should throw an error but probably returns Null. You may have to build the query in code. This is not hard but can be frustrating and timeconsuming. TxtSQLF = "first part of code that is always the same. Watch the use of single and double quotes" TxtSQLB = " the rest of the code" If Me.[cbxEquipment] then TxtSQL = TxtSQLF & "Where cbxEquipment = '" & me.cbxequioment & "' " & TxtSQLB Else TxtSQL = TxtSQLF & TxtSQLB End If is a quick example. Then run Msgbox TxtSql until it looks right, watching for bad quotes, etc. Finally, when it is right you can run it from DoCmd. |
#5
|
|||
|
|||
Specifying conditional Criteria in a Query or Report
Stephen @ ZennHAUS wrote:
Thanks for your suggestions Mike. I am familiar with building queries in code and find it quite easy to do. What I don't know in this case is how do I pass the resulting dynaset to the report then. You don't need to. All you need to do is build the WHERE clause and pass it to the report using the WhereCondition argument of the OpenReport method. Here's the online help for OpenReport: OpenReport Method See Also Applies To Example Specifics The OpenReport method carries out the OpenReport action in Visual Basic. expression.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs) expression Required. An expression that returns a DoCmd object. ReportName Required Variant. A string expression that's the valid name of a report in the current database. If you execute Visual Basic code containing the OpenReport method in a library database, Microsoft Access looks for the report with this name, first in the library database, then in the current database. View Optional AcView. The view to apply to the specified report. AcView can be one of these AcView constants. acViewDesign acViewNormal default Prints the report immediately. acViewPivotChart Not supported. acViewPivotTable Not supported. acViewPreview FilterName Optional Variant. A string expression that's the valid name of a query in the current database. WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE. WindowMode Optional AcWindowMode. AcWindowMode can be one of these AcWindowMode constants. acDialog acHidden acIcon acWindowNormal default OpenArgs Optional Variant. Sets the OpenArgs property. Remarks For more information on how the action and its arguments work, see the action topic. The maximum length of the WhereCondition argument is 32,768 characters (unlike the Where Condition action argument in the Macro window, whose maximum length is 256 characters). You can leave an optional argument blank in the middle of the syntax, but you must include the argument's comma. If you leave one or more trailing arguments blank, don't use a comma following the last argument you specify. Example The following example prints Sales Report while using the existing query Report Filter. DoCmd.OpenReport "Sales Report", acViewNormal, "Report Filter" -- HTH, Bob Barrows |
#6
|
|||
|
|||
Specifying conditional Criteria in a Query or Report
I would build my search string wiht vba code
I have a form with 5 textboxes cmbVender, txtProject, txtPO, txtSize and cmbType The query underlying the form uses venderid, projectid, ponum, OD, Type and active fields The user can enter values in any of the boxes and it will generate a report selected by those criteria lots of ANDs I look to see if the control has data and if it does I add an 'AND' phrase tho the criterial string my simple minded code----------------------- Private Sub cmdSearchPO_Click() On Error GoTo Err_cmdSearchPO_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "SearchForm" stLinkCriteria = "active = true" If Not IsNull(Me.cmbVender) Then stLinkCriteria = stLinkCriteria & " and [VenderID] = '" & Me![cmbVender] & "'" End If If Not IsNull(Me.txtproject) Then stLinkCriteria = stLinkCriteria & " and projectid = '" & Me.txtproject & "'" End If If Not IsNull(Me.txtPO) Then stLinkCriteria = stLinkCriteria & " and PONum = '" & Me.txtPO & "'" End If If Not IsNull(Me.cmbType) Then stLinkCriteria = stLinkCriteria & " and type = '" & Me.cmbType & "'" End If If Not IsNull(Me.txtSize) Then stLinkCriteria = stLinkCriteria & " and (OD) like '" & Me.txtSize & "*'" End If DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria ---------- End of cod "Stephen @ ZennHAUS" wrote in message ... Hi Guys and Gals, I have a number of reports each fed by a different query. In each query I have criteria supplied by a form (for example, Between [forms]![frmDateRange]![txtDateFrom] And [forms]![frmDateRange]![txtDateTo]) which works just fine. However, now I want to add the option to specify additional criteria using the form such as an equipment number, operator etc. Naturally, each criteria is not being specified each time the report is being run and so I need to include certain criteria only when it is chosen in the form. So I used an IIf statement to try and make the criteria only apply when the combo box on the form is filled out (for example, IIf([forms]![frmDateRange]![cbxEquipment] Not IsNull, [forms]![frmDateRange]![cbxEquipment]) ) When the query is run from the form, if the combobox has something in it, it works fine, but if there is nothing in the combobox, the report displays nothing. If I remove the IIF statement from the criteria, the report displays all of the records regardless of which equipment it is (as you would expect). What am I doing wrong with my IIf statement? Is there a better way to do this especially considering some reports will have up to 5 criteria specified or not. Cheers Stephen @ ZennHAUS |
#7
|
|||
|
|||
Specifying conditional Criteria in a Query or Report
Thanks Bob.
Works a treat. Don't know why I hadn't been able to work that out on my own previously. Cheers Stephen @ ZennHAUS "Bob Barrows" wrote in message ... Stephen @ ZennHAUS wrote: Thanks for your suggestions Mike. I am familiar with building queries in code and find it quite easy to do. What I don't know in this case is how do I pass the resulting dynaset to the report then. You don't need to. All you need to do is build the WHERE clause and pass it to the report using the WhereCondition argument of the OpenReport method. Here's the online help for OpenReport: OpenReport Method See Also Applies To Example Specifics The OpenReport method carries out the OpenReport action in Visual Basic. expression.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs) expression Required. An expression that returns a DoCmd object. ReportName Required Variant. A string expression that's the valid name of a report in the current database. If you execute Visual Basic code containing the OpenReport method in a library database, Microsoft Access looks for the report with this name, first in the library database, then in the current database. View Optional AcView. The view to apply to the specified report. AcView can be one of these AcView constants. acViewDesign acViewNormal default Prints the report immediately. acViewPivotChart Not supported. acViewPivotTable Not supported. acViewPreview FilterName Optional Variant. A string expression that's the valid name of a query in the current database. WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE. WindowMode Optional AcWindowMode. AcWindowMode can be one of these AcWindowMode constants. acDialog acHidden acIcon acWindowNormal default OpenArgs Optional Variant. Sets the OpenArgs property. Remarks For more information on how the action and its arguments work, see the action topic. The maximum length of the WhereCondition argument is 32,768 characters (unlike the Where Condition action argument in the Macro window, whose maximum length is 256 characters). You can leave an optional argument blank in the middle of the syntax, but you must include the argument's comma. If you leave one or more trailing arguments blank, don't use a comma following the last argument you specify. Example The following example prints Sales Report while using the existing query Report Filter. DoCmd.OpenReport "Sales Report", acViewNormal, "Report Filter" -- HTH, Bob Barrows |
Thread Tools | |
Display Modes | |
|
|