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.OpenRepor t "rptMailingList ", acViewPreview, "qryMailingList Christmas"
qryMailingListC hristmas sql:
SELECT DISTINCTROW tblClients.Bill ingName, tblClients.Acco untType,
tblClients.Bill ingAddress, tblClients.Cont act, tblClients.Bill ingCity,
tblClients.Bill ingState, tblClients.Bill ingZip,
Year([tblProjects].[StartDate]) AS Expr1
FROM tblClients INNER JOIN tblProjects ON tblClients.Clie ntID =
tblProjects.Cli entID
WHERE (((tblClients.A ccountType)="Re sidential") AND
((Year([tblProjects].[StartDate]))=Year(Date()) )) ORDER BY
tblClients.Bill ingName;
If I run qryMailingListC hristmas 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.Sta rtDate. 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? 4 3829
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.OpenRepor t "rptMailingList ", acViewPreview, "qryMailingList Christmas"
qryMailingListC hristmas sql:
SELECT DISTINCTROW tblClients.Bill ingName, tblClients.Acco untType,
tblClients.Bill ingAddress, tblClients.Cont act, tblClients.Bill ingCity,
tblClients.Bill ingState, tblClients.Bill ingZip,
Year([tblProjects].[StartDate]) AS Expr1
FROM tblClients INNER JOIN tblProjects ON tblClients.Clie ntID =
tblProjects.Cli entID
WHERE (((tblClients.A ccountType)="Re sidential") AND
((Year([tblProjects].[StartDate]))=Year(Date()) )) ORDER BY
tblClients.Bill ingName;
If I run qryMailingListC hristmas 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.Sta rtDate. 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?
"Jimmy" <no**@none.comw rote in
news:e3******** *************@f e10.news.easyne ws.com:
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.OpenRepor t "rptMailingList ", acViewPreview,
"qryMailingList Christmas"
qryMailingListC hristmas sql:
SELECT DISTINCTROW tblClients.Bill ingName,
tblClients.Acco untType, tblClients.Bill ingAddress,
tblClients.Cont act, tblClients.Bill ingCity,
tblClients.Bill ingState, tblClients.Bill ingZip,
Year([tblProjects].[StartDate]) AS Expr1 FROM tblClients INNER
JOIN tblProjects ON tblClients.Clie ntID = tblProjects.Cli entID
WHERE (((tblClients.A ccountType)="Re sidential") AND
((Year([tblProjects].[StartDate]))=Year(Date()) )) ORDER BY
tblClients.Bill ingName;
If I run qryMailingListC hristmas 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.Sta rtDate. 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
"Bob Quintal" <rq******@sPAmp atico.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 qryMailingListC hristmas, 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.
"Jimmy" <no**@none.comw rote in
news:R1******** *************@f e10.news.easyne ws.com:
>
"Bob Quintal" <rq******@sPAmp atico.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
qryMailingListC hristmas, 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: (Pete Cresswell) |
last post by:
I've got some SQL that works as far as returning a recordset from a series of
UNION statements.
viz:
SELECT whatever
UNION this
UNION that
UNION other
|
by: Mike Cooper |
last post by:
Hi everyone,
This is a tough one. I have a database full of solicitations,
identifying a customer and recording initial call, first followup,
second followup, etc.
My boss want to be able to generate a report showing a list of
customer who were call between two different dates that he types into
a form. That, I have done. My boss types in his two dates, the SQL
statement searches through the follow-up call fields for a date
|
by: carl.barrett |
last post by:
Hi,
I have a list box that displays 2 columns. Behind it sits a query with
five columns. These are Column1 (DOB), column2 (a concatenated string
of Surname Forname, Title), Column3 (Surname), column4 (Forename) and
column5 (title). Columns 3,4 and 5 are not shown in the list box only
the first two.
DOB Name: &" "&&", "&
|
by: Randall Arnold |
last post by:
I have a Microsoft Access report (built on a SQL Server 2000 query) I have
exported as xml/html. I include a stylesheet in the original export for
formatting of the report. The schema is separate. When I preview this in
my Visual web developer Express project it looks almost perfect (VBA
generated color formatting is missing, but that's no show-stopper). So it
works up to here.
For unknown reasons I cannot use the Live Data option in...
|
by: amith.srinivas |
last post by:
Hi all,
From a word macro in VBA, I am trying to create a report in
access DB. The report is based on a query with a parameter. I am using
Set rpt = Application.CreateReport
rpt.RecordSource = <<Query_Name>>
Once I create the report, I m trying to create labels to display the
Report title in the Report Header section.
| |
by: Mr.Kane |
last post by:
Here's my dilemma. I am putting together a trend report using
PivotCharts and so the query that I am trying to construct basically
would look at the "Date_Entered" for a record and if the "day" portion
of the Date is <= 15 (ie 1/1/2006 - 1/15/2006) it will populate a temp
column with the actual month and year.
However if the "day" portion of the Date is > 15 (ie 1/16/2006 -
1/31/2006) it will populate a temp column with the following...
|
by: Mayhem05 |
last post by:
I have an Access 2003 database that I need to write some VBA code for
to populate a table. The table is based on a query I have built in
Access queries. Right now I have 2 parameters that are passed to the
query from a form (DateFrom and DateTo). When I open the form and
populate the variables (DateFrom and DateTo) then open the query it
works fine.
My problem is that I need to do this from VBA coding and pass the 2
parameters to the...
|
by: John |
last post by:
Hi
I have a parameterised query for a report like this;
PARAMETERS Event_ID Short;
SELECT DISTINCTROW Events., ...
FROM Events
WHERE (((Events.)=));
Now I am trying to run the report while passing the parameter value like
|
by: vljones |
last post by:
I have a report with sub reports. The sub reports are query based. The queries perform a Count. Where no data exists in the table, no results are provided.
Is there a way to populate the sub report field with a 0 when this occurs?
Have tried IIF function in query with no luck.
SELECT QuoteItems.QuoteID, QuoteItems.CleanID, QuoteItems.ContactID, IIf(() Is Null,0,Count()) AS CountOfCleanID
FROM QuoteItems
GROUP BY...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |