Assigning a criteria

[szag via AccessMonster.com]

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.

 

[Duane Hookom]

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.

 

[szag via 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?

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.

 

[Marshall Barton]

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

 

[szag via AccessMonster.com]

This is great Marshall!

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

 

[szag via 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

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

 

[Marshall Barton]

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.

 

[Duane Hookom]

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

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

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

--



.

 

[szag via AccessMonster.com]

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

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.

 

[szag via AccessMonster.com]

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.

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]

 

[Marshall Barton]

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]

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.

 

[Duane Hookom]

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

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

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

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

--



.

 

[szag via AccessMonster.com]

Terrific - everything works. Thanks much for sticking with me.

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]