Query Based on Query

[Steven Stewart]

Hi there,

I have a query (QueryB) that is based on the results of another query
(QueryA).

When I open QueryB itself, it displays the exact records that I want. I
have a report based on QueryB, and when I open it directly, it displays
what I want. When I open the report from a command button on a form
using this code:

DoCmd.OpenReport "rpt_QueryB", acViewPreview, "QueryB"

...I get prompted for parameters.

Since I can open the query and the report itself without getting
prompted for parameters, why do I get prompted for them when I use
docmd.openreport? I don't want it to do this.

Thanks!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Wayne Morgan]

Try just this

DoCmd.OpenReport "rpt_QueryB", acViewPreview

--
Wayne Morgan
Microsoft Access MVP
"Steven Stewart" <o6***@unb.ca> wrote in message
news:3f*********************@news.frii.net...

Hi there,

I have a query (QueryB) that is based on the results of another query
(QueryA).

When I open QueryB itself, it displays the exact records that I want. I
have a report based on QueryB, and when I open it directly, it displays
what I want. When I open the report from a command button on a form
using this code:

DoCmd.OpenReport "rpt_QueryB", acViewPreview, "QueryB"

..I get prompted for parameters.

Since I can open the query and the report itself without getting
prompted for parameters, why do I get prompted for them when I use
docmd.openreport? I don't want it to do this.

Thanks!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steven Stewart]

Hi there,

I tried "DoCmd.OpenReport "rpt_QueryB", acViewPreview" and this did work
(it didn't ask me for parameters), but now the problem has come back.
In actuality, I need to specify the where clause in this
docmd.openreport....so I have this:

"DoCmd.OpenReport "rpt_QueryB", acViewPreview,,strSQL"

...and yet again it is prompting me for parameters. Any thoughts?
Without the strSQL it won't prompt me for parameters, but I do need that
there. Here is strSQL:

strSQL = "((([subquery_YTD_Prov_A]![Province])='" & lstProvince.Value &
"') AND (([subquery_YTD_Prov_A]![Month])>= " & getMonth(lstMonth2.Value)
& " Or ([subquery_YTD_Prov_A]![Month])<=" & getMonth(lstMonths.Value) &
") AND (([subquery_YTD_Prov_A]![Year])>= " & txtStartYear & " Or
([subquery_YTD_Prov_A]![Year])<= " & txtYear & " ))"

Basically, I want the query to be based on values from the form. I want
the query to return values in a certain month/year range (i.e. 2/2002 to
4/2003) and I want the province specified.

Thansk for any help!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Quintal]

Steven Stewart <o6***@unb.ca> wrote in
news:3f*********************@news.frii.net:

Hi there,

I tried "DoCmd.OpenReport "rpt_QueryB", acViewPreview" and
this did work (it didn't ask me for parameters), but now the
problem has come back. In actuality, I need to specify the
where clause in this docmd.openreport....so I have this:

"DoCmd.OpenReport "rpt_QueryB", acViewPreview,,strSQL"

..and yet again it is prompting me for parameters. Any
thoughts? Without the strSQL it won't prompt me for
parameters, but I do need that there. Here is strSQL:

strSQL = "((([subquery_YTD_Prov_A]![Province])='" &
lstProvince.Value & "') AND (([subquery_YTD_Prov_A]![Month])>=
" & getMonth(lstMonth2.Value) & " Or
([subquery_YTD_Prov_A]![Month])<=" & getMonth(lstMonths.Value)
& ") AND (([subquery_YTD_Prov_A]![Year])>= " & txtStartYear &
" Or ([subquery_YTD_Prov_A]![Year])<= " & txtYear & " ))"
You have a bunch of parameters in strSQL that the jet engine cannot
resolve, because your where clause is all messed up.

strSQL =
"[subquery_YTD_Prov_A].[Province]='" _
& me.lstProvince.Value & _
& "' " _
& "AND [subquery_YTD_Prov_A].[Month] >= " _
& getMonth(me.lstMonth2.Value) _
& " AND [subquery_YTD_Prov_A].[Month] <= "
& getMonth(me.lstMonths.Value) _
& " AND [subquery_YTD_Prov_A].[Year] >= " _
& me.txtStartYear _
& " AND [subquery_YTD_Prov_A].[Year] <= "
& me.txtYear

should work a lot better.

You had ! separators where . is required and no proper form
identifiers (me.), plus a whole lot of useless parentheses.

You also had some OR statements that really want to be AND
statements.

Bob Q.


Basically, I want the query to be based on values from the
form. I want the query to return values in a certain
month/year range (i.e. 2/2002 to 4/2003) and I want the
province specified.

Thansk for any help!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steven Stewart]

Thanks for the syntax lesson!..yes that, indeed, solves it, and I have
learned! This is, obviously, new stuff for me. Thanks again..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pieter Linden]

Steven Stewart <o6***@unb.ca> wrote in message news:<3f*********************@news.frii.net>...

Hi there,

I tried "DoCmd.OpenReport "rpt_QueryB", acViewPreview" and this did work
(it didn't ask me for parameters), but now the problem has come back.
In actuality, I need to specify the where clause in this
docmd.openreport....so I have this:

"DoCmd.OpenReport "rpt_QueryB", acViewPreview,,strSQL"

..and yet again it is prompting me for parameters. Any thoughts?
Without the strSQL it won't prompt me for parameters, but I do need that
there. Here is strSQL:

strSQL = "((([subquery_YTD_Prov_A]![Province])='" & lstProvince.Value &
"') AND (([subquery_YTD_Prov_A]![Month])>= " & getMonth(lstMonth2.Value)
& " Or ([subquery_YTD_Prov_A]![Month])<=" & getMonth(lstMonths.Value) &
") AND (([subquery_YTD_Prov_A]![Year])>= " & txtStartYear & " Or
([subquery_YTD_Prov_A]![Year])<= " & txtYear & " ))"

Basically, I want the query to be based on values from the form.
you have to reference the WHOLE "path" to the control you're
referencing, not just the control itself. the Control is on a form (in
that form's controls collection), which is in the forms collection...

Forms![MyForm]![MyControlOnMyForm]
strSQL = "((([subquery_YTD_Prov_A]![Province])='" & [FORMS]![MYFORM]!lstProvince.Value &
"') AND (([subquery_YTD_Prov_A]![Month])>= " & getMonth([FORMS]![MYFORM]!lstMonth2.Value)
& " Or ([subquery_YTD_Prov_A]![Month])<=" & getMonth([FORMS]![MYFORM]!lstMonths.Value) &
") AND (([subquery_YTD_Prov_A]![Year])>= " & txtStartYear & " Or
([subquery_YTD_Prov_A]![Year])<= " & txtYear & " ))"

[Steven Stewart]

Hi there,

Although I said it worked, I was mistaken. I had something REMed out
that shouldn't have been, making me think it had worked. So I tried
what was suggested:

strSQL =
"[subquery_YTD_Prov_A].[Province]='" _
& me.lstProvince.Value & _
& "' " _
& "AND [subquery_YTD_Prov_A].[Month] >= " _
& getMonth(me.lstMonth2.Value) _
& " AND [subquery_YTD_Prov_A].[Month] <= "
& getMonth(me.lstMonths.Value) _
& " AND [subquery_YTD_Prov_A].[Year] >= " _
& me.txtStartYear _
& " AND [subquery_YTD_Prov_A].[Year] <= "
& me.txtYear
DoCmd.OpenReport "rpt_YTD_By_Province", acViewPreview,
"subquery_YTD_Prov_B", strSQL

...and I still get prompted for parameters (subquery_YTD_Prov_A.Month
and .Year).

Why would it prompt me for those values when it is getting those values
in the code? I tried Val(me.txtYear) but that makes no difference.

What I have is a query (subquery_YTD_Prov_A) that works fine. Every
record returned in that query has a Month, Year, and Province field.
For the query I am doing ON that query, I want to only return the
records of a specific date range (month/year to month/year) for a
specific province. It's worth noting that it does not prompt me for the
province parameter....only the Month and Year.

I have named this second query "subquery_YTD_Prov_B" and if I open the
object directly, it works fine, but with the docmd from code it is not
working fine.

That is what I am trying to do, but I don't understand why I am getting
prompted for parameters still. Thanks again for any assistance.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Wayne Morgan]

Have you tried "hard coding" numbers in as a test instead of using the
references to the form's controls?

--
Wayne Morgan
Microsoft Access MVP
"Steven Stewart" <o6***@unb.ca> wrote in message
news:3f*********************@news.frii.net...

Hi there,

Although I said it worked, I was mistaken. I had something REMed out
that shouldn't have been, making me think it had worked. So I tried
what was suggested:

strSQL =
"[subquery_YTD_Prov_A].[Province]='" _
& me.lstProvince.Value & _
& "' " _
& "AND [subquery_YTD_Prov_A].[Month] >= " _
& getMonth(me.lstMonth2.Value) _
& " AND [subquery_YTD_Prov_A].[Month] <= "
& getMonth(me.lstMonths.Value) _
& " AND [subquery_YTD_Prov_A].[Year] >= " _
& me.txtStartYear _
& " AND [subquery_YTD_Prov_A].[Year] <= "
& me.txtYear
DoCmd.OpenReport "rpt_YTD_By_Province", acViewPreview,
"subquery_YTD_Prov_B", strSQL

..and I still get prompted for parameters (subquery_YTD_Prov_A.Month
and .Year).

Why would it prompt me for those values when it is getting those values
in the code? I tried Val(me.txtYear) but that makes no difference.

What I have is a query (subquery_YTD_Prov_A) that works fine. Every
record returned in that query has a Month, Year, and Province field.
For the query I am doing ON that query, I want to only return the
records of a specific date range (month/year to month/year) for a
specific province. It's worth noting that it does not prompt me for the
province parameter....only the Month and Year.

I have named this second query "subquery_YTD_Prov_B" and if I open the
object directly, it works fine, but with the docmd from code it is not
working fine.

That is what I am trying to do, but I don't understand why I am getting
prompted for parameters still. Thanks again for any assistance.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steven Stewart]

Hard-coding in the numbers made no difference. It still prompted me for
parameters.

For testing purposes, I simplified it down to setting the criteria for
just one field: Province...

------
strSQL = "[subquery_Prov_A]![Province] = '" & lstProvince.Value & "'"

DoCmd.OpenReport "rpt_YTD_By_Province", acViewPreview, subquery_Prov_B,
strSQL
-------

When I run this, I always get prompted for the parameter
"subquery_Prov_A!Province".

If I go directly to the queries design view and "hard-code" the criteria
into subquery_Prov_A and then open up subquery_Prov_B, it works
perfectly; however, I need to set the criteria based on form controls
before opening the report from code.

Now, even if I try...

strSQL = "[subquery_Prov_A]![Province] = 'British Columbia'"

...I still get prompted for that parameter.

So, this simply may not be possible to do.

Alas, I just realized that I could refer to the form controls directly
from the field's criteria in design view. Problem solved!

Thanks for all the help out there.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!