Assigning a criteria

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.

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

.

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