What's wrong with this ?

  • Thread starter Thread starter Spidey3721
  • Start date Start date
S

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
 
Hello Spidey,

Instead of:
strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"
Click to expand...

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

Spidey3721 said:
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
Click to expand...
 
[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...






Cheryl Fischer said:
Hello Spidey,

Instead of:
strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"
Click to expand...

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

Spidey3721 said:
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
Click to expand...
Click to expand...
 
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













Cheryl Fischer said:
Hello Spidey,

Instead of:
strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"
Click to expand...

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

Spidey3721 said:
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
Click to expand...
Click to expand...
 
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

Spidey3721 said:
[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...






Cheryl Fischer said:
Hello Spidey,

Instead of:
strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"
Click to expand...

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

Spidey3721 said:
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
Click to expand...
Click to expand...
bound
to
the [category] field.

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

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


DoCmd.OpenReport stDocName, acPreview, strFilter
Click to expand...
Click to expand...
Click to expand...
 
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

Spidey3721 said:
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













Cheryl Fischer said:
Hello Spidey,

Instead of:
strFilter = "[Master Cost Codes].[Category] = Forms![custom cost
report].[catcombo]"
Click to expand...

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

Spidey3721 said:
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
Click to expand...
Click to expand...
bound
to
the [category] field.

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

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


DoCmd.OpenReport stDocName, acPreview, strFilter
Click to expand...
Click to expand...
Click to expand...
 
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)
 
Back
Top