Assigning a criteria
in [Reports]
|
From: Marshall Barton on 15 Apr 2010 12:36 szag via AccessMonster.com wrote: >cboxActive - a combobox with "Yes" or "No" as choices >Project_Active? - Just a text field > >One thing I added was the = "Yes" on the first line - don't I need that? Yes, if that's the value of the combo box, then that's what you need to use. > >I selected "Yes" I my form for the cboxActive field, than ran the code and >got: > >Run time error 424 - object required... the debug takes me to the line: > >DocCmd.OpenReport "R_ProjSetup_Multiple", acViewPreview, , strWhere It's DoCmd, not DocCmd -- Marsh MVP [MS Access] >Marshall Barton wrote: >>>Spoke to soon. Here is my code: >>> >>[quoted text clipped - 6 lines] >>> >>>I am getting a run time error 13 - type mismatch error >> >>Is cboxActive a check box? >> >>What is the Project_Active? field's data type and values in >>the table? >> >>Note that if you must use a name with non alphanumeric >>characters, then you must also enclose the name in [ ] >> strWhere = "[Project_Active?]='Yes' " >>That error implies that the field is not a text field with >>the string Yes in it, but with that funky ? in the name I >>don't know what Access is doing with it. >>
From: Duane Hookom on 15 Apr 2010 13:56 Fixing a couple issues including the data types and DocCmd typo: Dim strWhere as String strWhere = "1=1 " If Me.cboxActive = "Yes" Then 'only active projects strWhere = strWhere & " AND [Project_Active]='Yes' " End If DoCmd.OpenReport "R_ProjSetup_Multiple", acViewPreview, , strWhere I always use the "1=1" because my criteria are generally not limited to one condition. I can continue to add more conditions as needed. -- Duane Hookom Microsoft Access MVP "Duane Hookom" wrote: > Do you actually have a question mark in a field name? If so, you must pay the > penalty of having to wrap the field name in []s. > > If the [Project_Active?] is a text field with values like 'Yes' and/or 'No' > you should be able to use something like: > > Dim strWhere as String > strWhere = "1=1 " > If Me.cboxActive = True Then 'only active projects > strWhere = strWhere & " AND [Project_Active?]='Yes' " > End If > DocCmd.OpenReport "R_ProjSetup_Multiple", acViewPreview, , strWhere > > -- > Duane Hookom > Microsoft Access MVP > > > "szag via AccessMonster.com" wrote: > > > Spoke to soon. Here is my code: > > > > If Me.cboxActive Then 'only active projects > > strWhere = "Project_Active?='Yes' " > > DocCmd.OpenReport "R_ProjSetup_Multiple", acViewPreview, , strWhere > > Else 'all projects: > > DocCmd.OpenReport "R_ProjSetup_Multiple", acViewPreview > > End If > > > > I am getting a run time error 13 - type mismatch error > > > > > > Marshall Barton wrote: > > >>I know am looking for the best way to accomplish the following: > > >> > > >[quoted text clipped - 3 lines] > > >>sometimes. What is the best way to assign an "active" or "all" status > > >>criteria into the report. > > > > > >Use buttons on a form to open the reports. If you have a > > >separate button for each condition, the code would look > > >like: > > >Dim strWhere As String > > > strWhere = "Status='Active' " > > > DocCmd.OpenReport "thereport", acviewPreview, , strWhere > > > > > >and for the all projects button: > > > DocCmd.OpenReport "thereport", acviewPreview > > > > > >Or, if you have something like a check box for users to > > >indicate they want active or all, a single button can do > > >either: > > > > > >Dim strWhere As String > > > If Me.thecheckbox Then 'only active projects > > > strWhere = "Status='Active' " > > > DocCmd.OpenReport "thereport", acviewPreview, , strWhere > > > Else 'all projects: > > > DocCmd.OpenReport "thereport", acviewPreview > > > End If > > > > > > > -- > > Message posted via AccessMonster.com > > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201004/1 > > > > . > >
From: szag via AccessMonster.com on 15 Apr 2010 17:18
Terrific - everything works. Thanks much for sticking with me. Duane Hookom wrote: >Fixing a couple issues including the data types and DocCmd typo: > > Dim strWhere as String > strWhere = "1=1 " > If Me.cboxActive = "Yes" Then 'only active projects > strWhere = strWhere & " AND [Project_Active]='Yes' " > End If > DoCmd.OpenReport "R_ProjSetup_Multiple", acViewPreview, , strWhere > >I always use the "1=1" because my criteria are generally not limited to one >condition. I can continue to add more conditions as needed. > >> Do you actually have a question mark in a field name? If so, you must pay the >> penalty of having to wrap the field name in []s. >[quoted text clipped - 47 lines] >> > > DocCmd.OpenReport "thereport", acviewPreview >> > > End If -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201004/1 |