Passing Qry as part of Rpt DoCmd.OpenReport

[JeffP->]

If the selected manufacturer = "_All" first value in list, then my query
sQry = "Year = " & ddlYear.Value
else: sQry = "Year = & ddlYear.Value & " and manufacturer = " & ddlManu.Value
.... also tried: "...=" & "'" & ddlManu.Value & "'"
DoCmd.OpenReport sReportName, acViewPreview, , sQry

Checking the value of sQry shows "Year = 2007 and manufactuer = 'TRMCO'" But
I still get the same data set as "Year = 2007" When I query by pasting the
same where clase into my data query I get only TRMCO w/in 2007.

Any help or insight ?

 

[Klatuu]

May I offer a better solution. Make your query for the record source of
your report plain vanilla with no filtering other than what would be needed
to include all records for the report. Then, rather than using the Filter
argument of the OpenReport, use the Where argument of the OpenReport. It is
like putting a Where clause in your query. Using the Where argument makes it
a lot more flexible.

 

[JeffP->]

I'm not sure where you derived the word "filter" from? I've marked your reply
"No" unhelpful, if you'd like me to correct it consider the following,
perhaps I am wrong.

Note that in my DoCmd there are two commas after view type, the first closes
viewtype and the second the empty filter value followed by the where clause.
DoCmd.OpenReport sReportName, acViewPreview, , sQry

And yes my data query is plain vanillia with no where clause only a single
join to get the year from the year/invoice order range table, and again my
year query works fine and if I paste in the value from sQry into my data
query add the keyword where it runs perfect returning my known data.

TIA

JeffP...

 

[Klatuu]

The argument you are using in the OpenReport method is the Filter argument.
You are, by your description, trying to filter the output of the report.
Maybe that is where I derived the word.

Sorry you did not understand the post. Maybe you need more explanation.
If you create a string that is a Where Clause without the word Where, you
use it in the Where argument of the OpenReport method. By definition, the
Where Clause Filters records (there is that word again).

If you need further explanation, please post back.

BTW, I don't mind you rating as unhelpful, but I do take exception to your
attitude. This site is not manned by paid Microsoft employees. We are all
volunteers. Most of us are Access developers working either on our own or
for a company doing Access development work. So, If you don't get the answer
you want, post back with a question for clarification. Being snippy to other
posters won't get you many answers for very long.

I suggest you visit this site:
http://www.mvps.org/access/netiquette.htm

 

[JeffP->]

On my snippyness, you mis-interpeted my notification, it was meant to advise
you nothing more.

Where: I havent included the keyword "where" in my DoCmd.Open report, should
I?

Perhaps I'm missing something, my understanding was that a filter is applied
after the data is returned and that the "where" clause was part of the
initial dataset creatation?

 

[Klatuu]

The FilterName argument is the name of a query used to filter the rows
returned. It works much like a subquery.

Actually, you don't use the word Where in the Where string for the Where
argument. You just put your criteria in as you would for an SQL query.

For example,

strWhere = "[Year] = " & Me.ddlYear & " AND manufacturer = " & Me.ddlMaun
DoCmd.OpenReport sReportName, acViewPreview, , , strWhere

Note I put your field name Year in brackets. Year should not be used as a
field name because it is an Acces reserved word. Using reserved words like
Year, Date, Time, Name, etc can confuse Access. Most of the time, you can
put the field name in brackets and it will resolve the problem, but the best
thing to do is not use reserved words. Also, I added Me. to your control
names. I am assuming they are controls on your form. It is always best to
qualify your control names with Me. or Me!

 

[Duane Hookom]

Jeff,
I think part of the issue is your use of "my query" in the following line:
If the selected manufacturer = "_All" first value in list, then my query
Also, the variable name "sQry" suggests a query rather than simply a where
condition.

The issue I see with your code is a missing quote following "else: sQry =
"Year= ...

Try
sQry = "1=1 "
If Not IsNull(Me.ddlYear) Then
sQry = sQry & " AND [Year]= " & Me.ddlYear
End If
If Nz(Me.ddlManu,"_All") <> "_All" Then
sQry = sQry & " AND Manufacturer = """ & _
Me.ddlManu & """ "
End If
DoCmd.OpenReport sReportName, acViewPreview, , sQry

 

[JeffP->]

First off my form just started working, and believe me I changed nothing in
my code. Perhaps I had a memory issue w/my system and re-opening cleared this.

Yes, sQry implies a query but it is just the contents of the where clause in
an SQL query. The year apparently is being cast as numeric and does not
require delimiter, although I'll try it that way so as to set the tone.

What I've learned: It appears that the DoCmd.Open report filter and where
components are both filters, the difference is lost on me, but I'm only using
the where clause and it's working now. Why it wasn't is also lost on me.

Most of the code was written by someone else, excepting for the use if
"where" in the OpenReport cmd. And yes, I should bracket or better yet not
use keywords. The year is ambigious anyway, it's actully the JobYear and
perhaps I should make it so; perhaps the novice that I'm assisting will be
able to get some better habits.

Originally my query did start w/1=1, which I use to be able to add "
and...." statements to in dynamic SQL in my sProcs. I may go back to that,
howver my form is accessing a query that already returns all records, but
I'll add it just the same.

Cheers, JeffP....

Jeff,
I think part of the issue is your use of "my query" in the following line:
If the selected manufacturer = "_All" first value in list, then my query
Also, the variable name "sQry" suggests a query rather than simply a where
condition.

The issue I see with your code is a missing quote following "else: sQry =
"Year= ...

Try
sQry = "1=1 "
If Not IsNull(Me.ddlYear) Then
sQry = sQry & " AND [Year]= " & Me.ddlYear
End If
If Nz(Me.ddlManu,"_All") <> "_All" Then
sQry = sQry & " AND Manufacturer = """ & _
Me.ddlManu & """ "
End If
DoCmd.OpenReport sReportName, acViewPreview, , sQry


--
Duane Hookom
Microsoft Access MVP

On my snippyness, you mis-interpeted my notification, it was meant to advise
you nothing more.

Where: I havent included the keyword "where" in my DoCmd.Open report, should
I?

Perhaps I'm missing something, my understanding was that a filter is applied
after the data is returned and that the "where" clause was part of the
initial dataset creatation?