What's wrong with this ?

[Spidey3721]

The following is the DoCmd I am trying to use to open a report from a form
that has a combobox ([catcombo]) - I want the report to only show records
with a category that matches this combobox. The report's record source is
currently set to the table, [Master Cost Codes] and has a textbox bound to
the [category] field.

Right now the command button is showing the report, but is not applying the
filter...

strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"
stDocName = "Custom Cost Report"


DoCmd.OpenReport stDocName, acPreview, strFilter

 

[Cheryl Fischer]

Hello Spidey,

Instead of:

strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"

Try this:

If the value of [catcombo] is numeric:
strFilter = "[Master Cost Codes].[Category] = " & Forms![custom cost
report].[catcombo]

If the value of [catcombo] is a string:
strFilter = "[Master Cost Codes].[Category] = " & Chr(34) &
Forms![custom cost
report].[catcombo] & Chr(34)

hth,



--
Cheryl Fischer
Law/Sys Associates
Houston, TX

The following is the DoCmd I am trying to use to open a report from a form
that has a combobox ([catcombo]) - I want the report to only show records
with a category that matches this combobox. The report's record source is
currently set to the table, [Master Cost Codes] and has a textbox bound to
the [category] field.

Right now the command button is showing the report, but is not applying the
filter...

strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"
stDocName = "Custom Cost Report"


DoCmd.OpenReport stDocName, acPreview, strFilter

 

[Spidey3721]

[catcombo] is a string and, although I have tried using

strFilter = "[Master Cost Codes].[Category] = " & Forms![custom cost
report].[catcombo]

I'm not sure what the chr(34)'s will do for me.

I think that I am misunderstanding how to use the OpenReport command. Can I
even place the strfilter in the Filtername argument of the openreport
command, or should I be using the WHERE argument somehow ? If so, how ?

I am trying to avoid having to reference an actual query/filter, which is
what the help file for the OpenReport tells me to use...

Hello Spidey,

Instead of:

strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"

Try this:

If the value of [catcombo] is numeric:
strFilter = "[Master Cost Codes].[Category] = " & Forms![custom cost
report].[catcombo]

If the value of [catcombo] is a string:
strFilter = "[Master Cost Codes].[Category] = " & Chr(34) &
Forms![custom cost
report].[catcombo] & Chr(34)

hth,



--
Cheryl Fischer
Law/Sys Associates
Houston, TX

The following is the DoCmd I am trying to use to open a report from a form
that has a combobox ([catcombo]) - I want the report to only show records
with a category that matches this combobox. The report's record source is
currently set to the table, [Master Cost Codes] and has a textbox bound to
the [category] field.

Right now the command button is showing the report, but is not applying the
filter...

strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"
stDocName = "Custom Cost Report"


DoCmd.OpenReport stDocName, acPreview, strFilter

 

[Spidey3721]

I actually got it to work by using the following:



strFilter = "[Category] = Forms![Custom Cost Report].[CatCombo]"


stDocName = "Custom Cost Report"
DoCmd.OpenReport stDocName, acPreview
With Reports![custom cost report].Report
.Filter = strFilter
.FilterOn = True
End With

But I would still like to know if I can do it directly in the OpenReport
command

Hello Spidey,

Instead of:

strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"

Try this:

If the value of [catcombo] is numeric:
strFilter = "[Master Cost Codes].[Category] = " & Forms![custom cost
report].[catcombo]

If the value of [catcombo] is a string:
strFilter = "[Master Cost Codes].[Category] = " & Chr(34) &
Forms![custom cost
report].[catcombo] & Chr(34)

hth,



--
Cheryl Fischer
Law/Sys Associates
Houston, TX

The following is the DoCmd I am trying to use to open a report from a form
that has a combobox ([catcombo]) - I want the report to only show records
with a category that matches this combobox. The report's record source is
currently set to the table, [Master Cost Codes] and has a textbox bound to
the [category] field.

Right now the command button is showing the report, but is not applying the
filter...

strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"
stDocName = "Custom Cost Report"


DoCmd.OpenReport stDocName, acPreview, strFilter

 

[Cheryl Fischer]

Chr(34) is the ascii code for a double-quote and, if you are filtering on a
string value, that value must be enclosed in quotes. So, if the value of
Forms![custom cost report].[catcombo] is, say, "Utilities", the string that
is passed will be:

[Master Cost Codes].[Category] = "Utilities"

You could stick a

MsgBox strFilter

in your code to see what is actually being passed to the OpenReport
command - I find that helps me sometimes.

hth,

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

[catcombo] is a string and, although I have tried using

strFilter = "[Master Cost Codes].[Category] = " & Forms![custom cost
report].[catcombo]

I'm not sure what the chr(34)'s will do for me.

I think that I am misunderstanding how to use the OpenReport command. Can I
even place the strfilter in the Filtername argument of the openreport
command, or should I be using the WHERE argument somehow ? If so, how ?

I am trying to avoid having to reference an actual query/filter, which is
what the help file for the OpenReport tells me to use...

Hello Spidey,

Instead of:

strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"

Try this:

If the value of [catcombo] is numeric:
strFilter = "[Master Cost Codes].[Category] = " & Forms![custom cost
report].[catcombo]

If the value of [catcombo] is a string:
strFilter = "[Master Cost Codes].[Category] = " & Chr(34) &
Forms![custom cost
report].[catcombo] & Chr(34)

hth,



--
Cheryl Fischer
Law/Sys Associates
Houston, TX

The following is the DoCmd I am trying to use to open a report from a form
that has a combobox ([catcombo]) - I want the report to only show records
with a category that matches this combobox. The report's record source is
currently set to the table, [Master Cost Codes] and has a textbox

bound

to

the [category] field.

Right now the command button is showing the report, but is not

applying
the

filter...

strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"
stDocName = "Custom Cost Report"


DoCmd.OpenReport stDocName, acPreview, strFilter

 

[Cheryl Fischer]

In Access, there is often more than one way to do something. Using the
Filter property of the Report is perfectly valid.

You can use the OpenReport method's Filter argument as long as you pass it a
valid SQL Where clause (which excludes the word "where").

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I actually got it to work by using the following:



strFilter = "[Category] = Forms![Custom Cost Report].[CatCombo]"


stDocName = "Custom Cost Report"
DoCmd.OpenReport stDocName, acPreview
With Reports![custom cost report].Report
.Filter = strFilter
.FilterOn = True
End With

But I would still like to know if I can do it directly in the OpenReport
command

Hello Spidey,

Instead of:

strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"

Try this:

If the value of [catcombo] is numeric:
strFilter = "[Master Cost Codes].[Category] = " & Forms![custom cost
report].[catcombo]

If the value of [catcombo] is a string:
strFilter = "[Master Cost Codes].[Category] = " & Chr(34) &
Forms![custom cost
report].[catcombo] & Chr(34)

hth,



--
Cheryl Fischer
Law/Sys Associates
Houston, TX

The following is the DoCmd I am trying to use to open a report from a form
that has a combobox ([catcombo]) - I want the report to only show records
with a category that matches this combobox. The report's record source is
currently set to the table, [Master Cost Codes] and has a textbox

bound

to

the [category] field.

Right now the command button is showing the report, but is not

applying
the

filter...

strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"
stDocName = "Custom Cost Report"


DoCmd.OpenReport stDocName, acPreview, strFilter

 

[Van T. Dinh]

Most of sample codes for OpenReport are fairly confusing.
When you only want to pass a String for the selection
criteria (i.e. similar to a Where clause without the word
Where), you actually use the "WhereCondition" argument,
NOT the "Filter" argument.

You only use the "Filter" argument if you want to pass the
name of a saved Query and in this case, Access will use
the Where clause of the saved Query to select the required
Records.

As you can see, using the Variable Name strFilter is
rather confusing because it was meant for
the "WhereCondition". I avoid this by using the Variable
name strWhereCon rather than strFilter. Thus, in your
case, I would use:

strWhereCon = "[Master Cost Codes].[Category] = " & _
Forms![custom cost report].[catcombo]
DoCmd.OpenReport stDocName, acPreview, , strWhereCon

Note the extra comma (c.f. your original OpenReport
statement) in front of the strWhereCon which indicate that
the value for the Filter argument is Null, i.e. I don't
want to use the Filter argument and the strWhereCon is
meant for the "WhereCondition" argument.

HTH
Van T. Dinh
MVP (Access)