Using a query to populate a report.

[Jimmy]

I have a form with a command button on it that is supposed to open up a
report and use a query to populate it.

DoCmd.OpenReport "rptMailingList", acViewPreview, "qryMailingListChristmas"

qryMailingListChristmas sql:

SELECT DISTINCTROW tblClients.BillingName, tblClients.AccountType,
tblClients.BillingAddress, tblClients.Contact, tblClients.BillingCity,
tblClients.BillingState, tblClients.BillingZip,
Year([tblProjects].[StartDate]) AS Expr1
FROM tblClients INNER JOIN tblProjects ON tblClients.ClientID =
tblProjects.ClientID
WHERE (((tblClients.AccountType)="Residential") AND
((Year([tblProjects].[StartDate]))=Year(Date()))) ORDER BY
tblClients.BillingName;

If I run qryMailingListChristmas I get the results I expect. Returns all
residential clients with a project start date in the current year. If,
however, I use that command button on the form I am prompted to enter
tblProjects.StartDate. If I enter any date, the report opens showing all
Residential clients. What is going on here? Why am I even being promted to
enter a date?

[salad]

Jimmy wrote:

I have a form with a command button on it that is supposed to open up a
report and use a query to populate it.

DoCmd.OpenReport "rptMailingList", acViewPreview, "qryMailingListChristmas"

qryMailingListChristmas sql:

SELECT DISTINCTROW tblClients.BillingName, tblClients.AccountType,
tblClients.BillingAddress, tblClients.Contact, tblClients.BillingCity,
tblClients.BillingState, tblClients.BillingZip,
Year([tblProjects].[StartDate]) AS Expr1
FROM tblClients INNER JOIN tblProjects ON tblClients.ClientID =
tblProjects.ClientID
WHERE (((tblClients.AccountType)="Residential") AND
((Year([tblProjects].[StartDate]))=Year(Date()))) ORDER BY
tblClients.BillingName;

If I run qryMailingListChristmas I get the results I expect. Returns all
residential clients with a project start date in the current year. If,
however, I use that command button on the form I am prompted to enter
tblProjects.StartDate. If I enter any date, the report opens showing all
Residential clients. What is going on here? Why am I even being promted to
enter a date?

Go into View/Sort&Group off the menu. Is there a sort on a field that
doesn't exist on the report?

[Bob Quintal]

"Jimmy" <no**@none.comwrote in
news:e3*********************@fe10.news.easynews.co m:

I have a form with a command button on it that is supposed to
open up a report and use a query to populate it.

DoCmd.OpenReport "rptMailingList", acViewPreview,
"qryMailingListChristmas"

qryMailingListChristmas sql:

SELECT DISTINCTROW tblClients.BillingName,
tblClients.AccountType, tblClients.BillingAddress,
tblClients.Contact, tblClients.BillingCity,
tblClients.BillingState, tblClients.BillingZip,
Year([tblProjects].[StartDate]) AS Expr1 FROM tblClients INNER
JOIN tblProjects ON tblClients.ClientID = tblProjects.ClientID
WHERE (((tblClients.AccountType)="Residential") AND
((Year([tblProjects].[StartDate]))=Year(Date()))) ORDER BY
tblClients.BillingName;

If I run qryMailingListChristmas I get the results I expect.
Returns all residential clients with a project start date in
the current year. If, however, I use that command button on
the form I am prompted to enter tblProjects.StartDate. If I
enter any date, the report opens showing all Residential
clients. What is going on here? Why am I even being promted
to enter a date?

From your comments:
1) The query runs ok.
3) The report fails when launched by the form
Missing is #2. The report works/fails when run from the database
window.
I suspect that the problem is in the report, not the query nor
the method to launch it.

In general if an object like a form or report prompts for a
value, suspect that there is a typo somewhere in the form or
report., specifically with a call to the field that's being
asked for, or in the call to the underlying query of the form or
report. Could the report be calling an old version of the query?

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Jimmy]

"Bob Quintal" <rq******@sPAmpatico.cawrote in message

>>

From your comments:
1) The query runs ok.
3) The report fails when launched by the form
Missing is #2. The report works/fails when run from the database
window.

The report runs fine from the database window as well. Perhaps a point of
interest, I'm not sure, but the underlying record source for the report is
qryClients while the query that the command button is attempting to use to
populate the report is qryMailingListChristmas, both of which are different
from the underlying query for the form itself. All three forms are,
however, variations of the same dataset.

I suspect that the problem is in the report, not the query nor
the method to launch it.

In general if an object like a form or report prompts for a
value, suspect that there is a typo somewhere in the form or
report., specifically with a call to the field that's being
asked for, or in the call to the underlying query of the form or
report. Could the report be calling an old version of the query?

See comment above about different queries. I'm not sure if that would play a
role. Also, I've checked each query and each call to those queries and there
are no mis-spellings.

[Bob Quintal]

"Jimmy" <no**@none.comwrote in
news:R1*********************@fe10.news.easynews.co m:

>
"Bob Quintal" <rq******@sPAmpatico.cawrote in message

>>>

From your comments:
1) The query runs ok.
3) The report fails when launched by the form
Missing is #2. The report works/fails when run from the
database window.

The report runs fine from the database window as well. Perhaps
a point of interest, I'm not sure, but the underlying record
source for the report is qryClients while the query that the
command button is attempting to use to populate the report is
qryMailingListChristmas, both of which are different from the
underlying query for the form itself. All three forms are,
however, variations of the same dataset.

>I suspect that the problem is in the report, not the query
nor the method to launch it.

In general if an object like a form or report prompts for a
value, suspect that there is a typo somewhere in the form or
report., specifically with a call to the field that's being
asked for, or in the call to the underlying query of the form
or report. Could the report be calling an old version of the
query?

See comment above about different queries. I'm not sure if
that would play a role. Also, I've checked each query and each
call to those queries and there are no mis-spellings.

is the field expr1 in your main query?

When you open a report using the filtername parameter, the
filter query has to include only the fields in the query that's
bound to the report. If you've added the field expr1, that will
cause your problem. It will not add the fields.

Add the expr1 to the qryClients that underlies the report, but
without the year(date()) in the criteria. that will fix your
problem.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com