Assigning a criteria

I know am looking for the best way to accomplish the following:

I often have situations where I need to interchange a criteria for the same
report. For instance I have a report that I want to look up just projects
that are active but I also need that same report to show all projects
sometimes. What is the best way to assign an "active" or "all" status
criteria into the report.

Thanks.

--
Message posted via http://www.accessmonster.com

I typically build a WHERE CONDITION in code that checks user entered/selected
values from controls and uses the criteria in the DoCmd.OpenReport method.

I expect there may be more than two status values so I present them to the
user in a multi-select list box. I use a generic function to loop through the
selected items of the list box. If no items in the list box are selected then
nothing is added to the WHERE CONDITION.

--
Duane Hookom
Microsoft Access MVP


"szag via AccessMonster.com" wrote:

I know am looking for the best way to accomplish the following:

I often have situations where I need to interchange a criteria for the same
report. For instance I have a report that I want to look up just projects
that are active but I also need that same report to show all projects
sometimes. What is the best way to assign an "active" or "all" status
criteria into the report.

Thanks.

--
Message posted via http://www.accessmonster.com

.

Thanks Duane - I am kind of a hack when it comes to code. Is it possible to
show a quick example of how the where condition would work with the control
that has an "Active", "Inactive" selection?

Duane Hookom wrote:
I typically build a WHERE CONDITION in code that checks user entered/selected
values from controls and uses the criteria in the DoCmd.OpenReport method.

I expect there may be more than two status values so I present them to the
user in a multi-select list box. I use a generic function to loop through the
selected items of the list box. If no items in the list box are selected then
nothing is added to the WHERE CONDITION.

I know am looking for the best way to accomplish the following:

[quoted text clipped - 5 lines]

Thanks.

--
Message posted via http://www.accessmonster.com

szag via AccessMonster.com wrote:

I know am looking for the best way to accomplish the following:

I often have situations where I need to interchange a criteria for the same
report. For instance I have a report that I want to look up just projects
that are active but I also need that same report to show all projects
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

--
Marsh
MVP [MS Access]

This is great Marshall!

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 http://www.accessmonster.com

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/For...ports/201004/1

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


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.

--
Marsh
MVP [MS Access]

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/For...ports/201004/1

.

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?

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

Full Code:
If Me.cboxActive = "Yes" 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

End Sub

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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/201004/1

Sorry Duane - I just keep battling:
I got rid of the question mark, but even before then I used your code and got
the same run time 424 error. To be clear both the Project_Active field and
the cboxActive field are simple text fields of "Yes" or "No", this is not a a
value Yes/No field in the table. I wasn't sure you understood that based on
your code of... strWhere = "1=1 "...below. Sorry I am a bit of a beginner but
I really want to solve this as this issue arises all the time and I end up
inefficiently adding to identical reports one for active records and one for
inactive records. This is so much more efficient if I can get it to work.

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

Spoke to soon. Here is my code:

[quoted text clipped - 34 lines]
DocCmd.OpenReport "thereport", acviewPreview
End If

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/201004/1