Subreport in Report Obtains Wrong Date With Query Criteria

[Maurita]

Hi, I hope someone can help me with a Subreport problem I can't seem
to figure out. I have a "Downtime-Query" and a "Downtime-Subquery".
Within the "Downtime-Query" I have a "Date" field with Criteria of,
Between [Enter Report START Date] AND [Enter Report END Date]. When
the "Downtime-Report" is opened, the query criteria is requested. The
problem I am having is that if the same date range (12-12-06 and
12-12-06) are put in the criteria the report and subreport are
correct, BUT, if I use a different date range (1-1-06 and 12-12-06), I
only get a single date on the subreport and the the correct date range
(1-1-06 thru 12-12-06) in the report.

How can I tie the query criteria to both the form and subform since I
have the pop up box asking for date criteria.

Thank you so very much.

Maurita Searcy

 

[Ken Sheridan]

Maurita:

Rather than using a simple parameter, create an unbound dialogue form,
frmDowntimeDlg say, with two text boxes, txtStart and txtEnd. In both the
queries reference the form's controls as the parameters. I'd suggest you use
a different method of defining the data range by looking for dates on or
after the start date and dates before the day after the end date. This
allows for the possibility of dates having a non-zero time of day element,
which unless you have taken specific steps in the table design to prevent
this is a theoretical possibility which would prevent records with dates on
the last day of the range being returned.

Also on you form add a couple of command buttons, one to preview the report
and one to print it. The button wizard can set these up for you easily or
you can put the code in the buttons' Click event procedures yourself, which
gives you the chance to validate that both dates have been entered:

If Not IsNull(Me.txtStart + Me.txtEnd) Then
DoCmd.OpenReport "YourReportName" ' print the report
Else
MsgBox "Both dates must be entered.", vbExclamation, "Invalid
Operation"
End If

For the code for the preview button just change the second line to:

DoCmd.OpenReport "YourReportName", View:=acViewPreview ' preview the
report

In the queries its also a good idea with date parameters to declare them as
DateTime as otherwise they might be interpreted by Access as arithmetic
expressions and give the wrong result. So taking this and the different way
of defining the date range into account each query would be something like
this:

PARAMETERS
Forms!frmDowntimeDlg!txtStart DATETIME,
Forms!frmDowntimeDlg!txtEnd DATETIME;
SELECT [Date], <more fields>
FROM [YourTable]
WHERE [Date] >= Forms!frmDowntimeDlg!txtStart
AND [Date] < Forms!frmDowntimeDlg!txtEnd+1;

Yours may well be more complex of course, but all you really have to do is
open each query in design view, switch to SQL view and add the PARAMETERS
clause, and amend the WHERE clause.

Instead of opening the report, open the form, enter the dates and click one
of the buttons.

You don't say on what fields, if any, the report and subreport are linked.
This might have a bearing on the fact that you are getting only one record in
the subform. Or it could be something in the query itself. Its difficult to
diagnose the cause of this without knowing more of the details.

Finally I'd recommend that you don't use Date as a field name as it’s the
name of a built in function and could cause confusion. Wrapping the field
name in square brackets should avoid this, but using a more explicit name
such as InvoiceDate, TransactionDate etc. would be far better.

Ken Sheridan
Stafford, England

Hi, I hope someone can help me with a Subreport problem I can't seem
to figure out. I have a "Downtime-Query" and a "Downtime-Subquery".
Within the "Downtime-Query" I have a "Date" field with Criteria of,
Between [Enter Report START Date] AND [Enter Report END Date]. When
the "Downtime-Report" is opened, the query criteria is requested. The
problem I am having is that if the same date range (12-12-06 and
12-12-06) are put in the criteria the report and subreport are
correct, BUT, if I use a different date range (1-1-06 and 12-12-06), I
only get a single date on the subreport and the the correct date range
(1-1-06 thru 12-12-06) in the report.

How can I tie the query criteria to both the form and subform since I
have the pop up box asking for date criteria.

Thank you so very much.

Maurita Searcy

 

[Maurita]

Maurita:

Rather than using a simple parameter, create an unbound dialogue form,
frmDowntimeDlg say, with two text boxes, txtStart and txtEnd. In both the
queries reference the form's controls as the parameters. I'd suggest you use
a different method of defining the data range by looking for dates on or
after the start date and dates before the day after the end date. This
allows for the possibility of dates having a non-zero time of day element,
which unless you have taken specific steps in the table design to prevent
this is a theoretical possibility which would prevent records with dates on
the last day of the range being returned.

Also on you form add a couple of command buttons, one to preview thereport
and one to print it. The button wizard can set these up for you easily or
you can put the code in the buttons' Click event procedures yourself, which
gives you the chance to validate that both dates have been entered:

If Not IsNull(Me.txtStart + Me.txtEnd) Then
DoCmd.OpenReport "YourReportName" ' print thereport
Else
MsgBox "Both dates must be entered.", vbExclamation, "Invalid
Operation"
End If

For the code for the preview button just change the second line to:

DoCmd.OpenReport "YourReportName", View:=acViewPreview ' preview thereport

In the queries its also a good idea with date parameters to declare them as
DateTime as otherwise they might be interpreted by Access as arithmetic
expressions and give thewrongresult. So taking this and the different way
of defining the date range into account each query would be something like
this:

PARAMETERS
Forms!frmDowntimeDlg!txtStart DATETIME,
Forms!frmDowntimeDlg!txtEnd DATETIME;
SELECT [Date], <more fields>
FROM [YourTable]
WHERE [Date] >= Forms!frmDowntimeDlg!txtStart
AND [Date] < Forms!frmDowntimeDlg!txtEnd+1;

Yours may well be more complex of course, but all you really have to do is
open each query in design view, switch to SQL view and add the PARAMETERS
clause, and amend the WHERE clause.

Instead of opening thereport, open the form, enter the dates and click one
of the buttons.

You don't say on what fields, if any, thereportandsubreportare linked.
This might have a bearing on the fact that you are getting only one record in
the subform. Or it could be something in the query itself. Its difficult to
diagnose the cause of this without knowing more of the details.

Finally I'd recommend that you don't use Date as a field name as it's the
name of a built in function and could cause confusion. Wrapping the field
name in square brackets should avoid this, but using a more explicit name
such as InvoiceDate, TransactionDate etc. would be far better.

Ken Sheridan
Stafford, England

Hi, I hope someone can help me with aSubreportproblem I can't seem
to figure out. I have a "Downtime-Query" and a "Downtime-Subquery".
Within the "Downtime-Query" I have a "Date" field with Criteria of,
Between [EnterReportSTART Date] AND [EnterReportEND Date]. When
the "Downtime-Report" is opened, the query criteria is requested. The
problem I am having is that if the same date range (12-12-06 and
12-12-06) are put in the criteria thereportandsubreportare
correct, BUT, if I use a different date range (1-1-06 and 12-12-06), I
only get a single date on thesubreportand the the correct date range
(1-1-06 thru 12-12-06) in thereport.

How can I tie the query criteria to both the form and subform since I
have the pop up box asking for date criteria.

Thank you so very much.

Maurita Searcy- Hide quoted text -

- Show quoted text -

Thank you Ken for your input, I sure appreciate the opportunity to
learn something new. Anyway, since the database is complete and
tested except for this report, I must leave the "Date" field as is,
but will definately remember to rename it in future databases.

As to "PARAMETERS", I understand the logic but am unclear as to where
to write the code in the query.

The field I am linking is the "Date" field. Since the owner wants to
search the report by different dates, I felt searching the "Date"
field would be the best choice.

Thanks for all your help.

Maurita

 

[Ken Sheridan]

Maurita:

The PARAMETERS clause comes right at the start of the query, before the
SELECT clause. To do in that way you'd switch the query to SQL view but you
can insert it in design view as well. For that you open the query in design
view and select Parameters form the Query menu. In the dialogue enter the
two parameters in the first two lines of the left column and select Date/Time
in the second column for each, so it should look like this:

Forms!frmDowntimeDlg!txtStart Date/Time
Forms!frmDowntimeDlg!txtEnd Date/Time

You must make sure that each parameter in the dialogue is *exactly* the same
as in the criteria for the query.

To enter the parameters as the criteria in design view using my amended
criteria, you'd put the following in the 'criteria' row of the Date column:

= Forms!frmDowntimeDlg!txtStart And [Date] < Forms!frmDowntimeDlg!txtEnd + 1

As regards the linking of the main report and its subreport the fact that
you are linking them on the Date column explains why the subreport is only
returning one row (BTW 'column' and 'row' are the same as 'field' and
'record'. They are technically more correct terms in tables; record and
field are really terms used in old style file systems where data was stored
in fixed length records in files and addressed by position not name).

When you link a subreport to its parent report on a column the subreport
will show only those rows where the value of the field matches the value of
the current row in the parent report, so yours showed just the record with
the date of the parent report's current row, which would be the last if the
subreport is in the report footer.

It may well be that yours don't need linking at all. If the two queries
return mutually exclusive sets of rows, with the rows returned restricted to
the same date range by the identical parameters in each one's query then you
can just leave the LinkMasterFields and LinkChildField properties of the
subreport control blank.

When a report and subreport are linked its most commonly where the parent
and subreport are based on tables which are related one-to-many, e.g. you
might have a main report based on Customers and a subreport based on Orders,
linked on CustomerID. The Orders subreport would be placed in the detail
section of the report below the customer data; name, address etc. When the
report is opened each customer would be listed once and below each would be a
list of that customer's orders. For an example take a look at the Catalog
report in the sample Northwind database which comes with Access; you'll see
how the report shows each category and below it by means of a subreport a
list of products in the category.

Ken Sheridan
Stafford, England

 

[Maurita]

Maurita:

The PARAMETERS clause comes right at the start of the query, before the
SELECT clause.  To do in that way you'd switch the query to SQL view but you
can insert it in design view as well.  For that you open the query in design
view and select Parameters form the Query menu.  In the dialogue enter the
two parameters in the first two lines of the left column and select Date/Time
in the second column for each, so it should look like this:

Forms!frmDowntimeDlg!txtStart   Date/Time
Forms!frmDowntimeDlg!txtEnd    Date/Time

You must make sure that each parameter in the dialogue is *exactly* the same
as in the criteria for the query.

To enter the parameters as the criteria in design view using my amended
criteria, you'd put the following in the 'criteria' row of the Date column:

= Forms!frmDowntimeDlg!txtStart And [Date] < Forms!frmDowntimeDlg!txtEnd + 1

As regards the linking of the mainreportand itssubreportthe fact that
you are linking them on the Date column explains why thesubreportis only
returning one row (BTW 'column' and 'row' are the same as 'field' and
'record'.  They are technically more correct terms in tables; record and
field are really terms used in old style file systems where data was stored
in fixed length records in files and addressed by position not name).

When you link asubreportto its parentreporton a column thesubreport
will show only those rows where the value of the field matches the value of
the current row in the parentreport, so yours showed just the record with
the date of the parentreport'scurrent row, which would be the last if thesubreportis in thereportfooter.

It may well be that yours don't need linking at all.  If the two queries
return mutually exclusive sets of rows, with the rows returned restrictedto
the same date range by the identical parameters in each one's query then you
can just leave the LinkMasterFields and LinkChildField properties of thesubreportcontrol blank.

When areportandsubreportare linked its most commonly where the parent
andsubreportare based on tables which are related one-to-many, e.g. you
might have a mainreportbased on Customers and asubreportbased on Orders,
linked on CustomerID.  The Orderssubreportwould be placed in the detail
section of thereportbelow the customer data; name, address etc. When thereportis opened each customer would be listed once and below each would be a
list of that customer's orders.  For an example take a look at the Catalogreportin the sample Northwind database which comes with Access; you'll see
how thereportshows each category and below it by means of asubreporta
list of products in the category.

Ken Sheridan
Stafford, England

Thank you Ken for your input, I sure appreciate the opportunity to
learn something new.  Anyway, since the database is complete and
tested except for thisreport, I must leave the "Date" field as is,
but will definately remember to rename it in future databases.

As to "PARAMETERS", I understand the logic but am unclear as to where
to write the code in the query.

The field I am linking is the "Date" field.  Since the owner wants to
search thereportby different dates, I felt searching the "Date"
field would be the best choice.

Thanks for all your help.

Maurita- Hide quoted text -

- Show quoted text -

Ken,

Thank you so very much. I understand what you mean by my report only
returning the current row of the parent record when I try to put in a
date parameter of various dates. Do you have any ideas what I can do
to fix this problem? The subreport has no linking to the form.

Thank you.

Maurita

 

[Ken Sheridan]

Maurita:

Simply delete the entries for the LinkMasterFields and LinkChildFields
properties in the properties sheet of the subreport control in the main
report in design view. That will stop the subform being restricted to just
the one date. The report does not need to be linked directly to the form in
any way. It’s the underlying queries on which the report and subform are
based which reference the controls on the form.

Ken Sheridan
Stafford, England

 

[Maurita]

Maurita:

Simply delete the entries for the LinkMasterFields and LinkChildFields
properties in the properties sheet of thesubreportcontrol in the mainreportin design view. That will stop the subform being restricted to just
the one date. Thereportdoes not need to be linked directly to the form in
any way. It's the underlying queries on which thereportand subform are
based which reference the controls on the form.

Ken Sheridan
Stafford, England








- Show quoted text -

Ken,

I have deleted all references to any MasterLink or ChildLink but I
still have the same problem. I still get the correct results for one
date, but a range of dates I get incorrect results. Any ideas?

I have checked and rechecked my queries and reports and have found no
links between the two. The only field I am linking on the subform is
"Date"

Thank you so very much for any ideas you can send my way.

Maurita

 

[Maurita]

Maurita:

Simply delete the entries for the LinkMasterFields and LinkChildFields
properties in the properties sheet of thesubreportcontrol in the mainreportin design view. That will stop the subform being restricted to just
the one date. Thereportdoes not need to be linked directly to the form in
any way. It's the underlying queries on which thereportand subform are
based which reference the controls on the form.

Ken Sheridan
Stafford, England








- Show quoted text -

Ken,,

I am sorry to ask again, but I need your expertise. I have deleted
all references to any Links in the queries and report, but I am still
having the same problem. If I plug in the same begin and end date the
report and subreport returns the correct data, but if I put in
different begin and end dates, all data is not returned. I have been
checking and rechecking the queries and forms and have found no
reference to linking the Master or Child fields.

Thank you for any help you can give.

Maurita

 

[Ken Sheridan]

Maurita:

Lets see if we can pin down where the problem is:

1. Open the dialogue form and enter the before and end dates, making sure
you press Enter after the second one. Keep this form open during the
following steps.

2. Go to the queries page of the database window and open each of the two
queries independently. Each should list the rows for the rage defined by the
dates you entered in the form. If they don't then one of the queries is
wrong. In which case post the SQL of the offending query here. If they are
OK close the queries and proceed to the next step below.

3. If the queries are OK then select the subreport in the reports page of
the database window and open it in independently of the main report. It
should show the same rows as those returned by the relevant query. If it
doesn't then it’s the subreport that is the problem. It could be that the
controls bound to the columns in the underlying query are in the wrong
section of the report, e.g. its header or footer. They should be in the
detail section. If the subreport is OK close it and proceed to the next step.

4. Open the main report. If the main report shows all the dates in the
range then that is OK. If not then there is something wrong with the main
report. If its OK then switch to report design view and select the subreport
control; that's the control in the main report which houses the subreport.
On the data tab of its properties sheet find the LinkMasterFields and
LinkChildFields properties. These, if I understand your set-up correctly,
should be empty. If they have anything in them then delete the contents.
Open the report again and see what you get.

5. If it still doesn't return the right rows I don't think there's anything
more I can do remotely, but if you want to mail the file to me, with some
dummy data if you prefer, then send it (preferably zipped) to the following
(munged) address and I'll take a look:

ken<at>ksheridan<dot>orangehome<dot>co<dot>uk

Ken Sheridan
Stafford, England

 

[Maurita]

Maurita:

Lets see if we can pin down where the problem is:

1. Open the dialogue form and enter the before and end dates, making sure
you press Enter after the second one. Keep this form open during the
following steps.

2. Go to the queries page of the database window and open each of the two
queries independently. Each should list the rows for the rage defined by the
dates you entered in the form. If they don't then one of the queries iswrong. In which case post the SQL of the offending query here. If they are
OK close the queries and proceed to the next step below.

3. If the queries are OK then select thesubreportin the reports page of
the database window and open it in independently of the mainreport. It
should show the same rows as those returned by the relevant query. If it
doesn't then it's thesubreportthat is the problem. It could be that the
controls bound to the columns in the underlying query are in thewrong
section of thereport, e.g. its header or footer. They should be in the
detail section. If thesubreportis OK close it and proceed to the next step.

4. Open the mainreport. If the mainreportshows all the dates in the
range then that is OK. If not then there is somethingwrongwith the mainreport. If its OK then switch toreportdesign view and select thesubreport
control; that's the control in the mainreportwhich houses thesubreport.
On the data tab of its properties sheet find the LinkMasterFields and
LinkChildFields properties. These, if I understand your set-up correctly,
should be empty. If they have anything in them then delete the contents.
Open thereportagain and see what you get.

5. If it still doesn't return the right rows I don't think there's anything
more I can do remotely, but if you want to mail the file to me, with some
dummy data if you prefer, then send it (preferably zipped) to the following
(munged) address and I'll take a look:

ken<at>ksheridan<dot>orangehome<dot>co<dot>uk

Ken Sheridan
Stafford, England









- Show quoted text -

Ken,

Thanks for the latest steps of instructions, I am going to go through
them to see how things are working. As a note, I could not use your
"dialogue form" because the code in the 'criteria row' kept giving me
an error message stating that there was invalid bracketing int he
code. I tried several different ways to bracket the code, but nothing
worked, so I had to go back to the way I had originally configured
calling up dates. The criteria code problem I had problems with was:

= Forms!frmDlg!txtStart And [Date] < Forms!frmDowntimeDlg!txtEnd + 1

I am so sorry to be such a bother. I am trying to figure out the
problem so I can learn, but nothing makes sense. As the database
currently is, the subqueries and subreports work correctly when tested
by themselves. When I use the subform in the form, I must use "Date"
as the LinkMaster or I get no results at all. Of course, this form is
critical to the client, so I must have it workable. I've spent hours
the past three days on this form that I can't charge for because it's
obviously my problem.

Thanks again for all your help. I truly appreciate all your ideas and
am going through each one.

Maurita

 

[Ken Sheridan]

Maurita:

I think you'll have to mail me the file if we are going rto make progress.
I'll probably be able to sort it out easily once the dog can see the rabbit,
but at the moment I don't have enough to go on.

Ken Sheridan
Stafford, England