OpenReport using WHERE criteria for nested queries

[aemAndy]

I have a table of transaction data.

I'm creating the report using the following steps -

Query1 - from the transaction table

Pulls "Group"(Group), "ID"(Group), "Trans#"(count), "Pd Amount"(Sum), "Req
Amount"(Sum) and calculates "Savings"(Sum) WHERE PdDate Is Not Null

Query 2 - from Query 1

Groups by "Group", "ID"(Count), "Trans#(count)"(Sum), "Pd Amount"(Sum), "Req
Amount"(Sum), "Savings"(sum)

Query 3 - from Query 2

Takes all the fields from Query 2, and calculates AvgTransSav, AvgIDSav,
AvgTransPerID.

Now I have a report that uses Query 3 for it's data.

I have a form that launches the report.

Can I specify beginning and end paid dates (limiting criteria in Query 1,
but not displayed) as part of the WHERE statement on the "DoCmd.OpenReport"
command?

I already know it won't automatically go back through the food chain of
queries and apply it to the first (or if it does, I've screwed something else
wrong).

Is there a way to specify, in the WHERE statement, that I want that
criterial applied to Query 1?

 

[Allen Browne]

As you found, you can only apply a WhereCondition on fields that are in the
report. Since the fields from your lowest level query are not in the report,
you need a different approach.

A simple approach is to have Query1 read the dates from a form. Assuming a
form named frmWhatDates with text boxes named txtStartDate and txtEndDate,
you would put this in the Criteria row of Query1 under the PdDate field:
Between [Forms].[frmWhatDates].[txtStartDate] And
[Forms].[frmWhatDates].[txtEndDate]

Set the Format property of both text boxes to Short Date so they accept only
valid dates. Add a command button to the form to OpenReport. When it opens,
the lowest level query reads the dates from the form, and you're home.

If that is not suitable, other possibilities involve using subqueries
instead of stacked queries, or actually altering the SQL property of the
QueryDef before you OpenReport.

 

[aemAndy]

Yes, I have been having the base-level query pull the dates from the form,
and that's been working fine. There're no problems with that, but a couple
of comments -

Several other advice-givers on other threads have expounded on the evils of
pulling criteria from a form, because the form has continue to be open, etc.
etc. Not an issue here, because the only way the end-user can get to the
report is through this form, and the only way to navigate back out is through
the form, so there's no worries about trying to pull the report without the
form.

What I was really hoping to do is to idiot-proof....err, end-user proof the
form in case of "null" values. I wanted to program in that if null values
were present in both beginning and end fields, it would pass no "where"
criteria, basically making the report all-inclusive.

When I try putting that in at the query level, I get no records returned.

IIf([Forms]![frmrptNetwSav]![RepStartDate]=Null And
[Forms]![frmrptNetwSav]![RepEndDate]=Null,Is Not Null,Between
[Forms]![frmrptNetwSav]![RepStartDate] And
[Forms]![frmrptNetwSav]![RepEndDate])

Same problems if I tried "Is Null" instead of "= Null", and if my output to
the criteria field is put in quotes or not.

As you found, you can only apply a WhereCondition on fields that are in the
report. Since the fields from your lowest level query are not in the report,
you need a different approach.

A simple approach is to have Query1 read the dates from a form. Assuming a
form named frmWhatDates with text boxes named txtStartDate and txtEndDate,
you would put this in the Criteria row of Query1 under the PdDate field:
Between [Forms].[frmWhatDates].[txtStartDate] And
[Forms].[frmWhatDates].[txtEndDate]

Set the Format property of both text boxes to Short Date so they accept only
valid dates. Add a command button to the form to OpenReport. When it opens,
the lowest level query reads the dates from the form, and you're home.

If that is not suitable, other possibilities involve using subqueries
instead of stacked queries, or actually altering the SQL property of the
QueryDef before you OpenReport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table of transaction data.

I'm creating the report using the following steps -

Query1 - from the transaction table

Pulls "Group"(Group), "ID"(Group), "Trans#"(count), "Pd Amount"(Sum), "Req
Amount"(Sum) and calculates "Savings"(Sum) WHERE PdDate Is Not Null

Query 2 - from Query 1

Groups by "Group", "ID"(Count), "Trans#(count)"(Sum), "Pd Amount"(Sum),
"Req
Amount"(Sum), "Savings"(sum)

Query 3 - from Query 2

Takes all the fields from Query 2, and calculates AvgTransSav, AvgIDSav,
AvgTransPerID.

Now I have a report that uses Query 3 for it's data.

I have a form that launches the report.

Can I specify beginning and end paid dates (limiting criteria in Query 1,
but not displayed) as part of the WHERE statement on the
"DoCmd.OpenReport"
command?

I already know it won't automatically go back through the food chain of
queries and apply it to the first (or if it does, I've screwed something
else
wrong).

Is there a way to specify, in the WHERE statement, that I want that
criterial applied to Query 1?

 

[aemAndy]

I forgot to add, your suggestion to alter the form's text box properies to
only accept valid data fields is a good one. The users can certainly enter a
wide enough date range if they want to pull "all". Thanks very much.

As you found, you can only apply a WhereCondition on fields that are in the
report. Since the fields from your lowest level query are not in the report,
you need a different approach.

A simple approach is to have Query1 read the dates from a form. Assuming a
form named frmWhatDates with text boxes named txtStartDate and txtEndDate,
you would put this in the Criteria row of Query1 under the PdDate field:
Between [Forms].[frmWhatDates].[txtStartDate] And
[Forms].[frmWhatDates].[txtEndDate]

Set the Format property of both text boxes to Short Date so they accept only
valid dates. Add a command button to the form to OpenReport. When it opens,
the lowest level query reads the dates from the form, and you're home.

If that is not suitable, other possibilities involve using subqueries
instead of stacked queries, or actually altering the SQL property of the
QueryDef before you OpenReport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table of transaction data.

I'm creating the report using the following steps -

Query1 - from the transaction table

Pulls "Group"(Group), "ID"(Group), "Trans#"(count), "Pd Amount"(Sum), "Req
Amount"(Sum) and calculates "Savings"(Sum) WHERE PdDate Is Not Null

Query 2 - from Query 1

Groups by "Group", "ID"(Count), "Trans#(count)"(Sum), "Pd Amount"(Sum),
"Req
Amount"(Sum), "Savings"(sum)

Query 3 - from Query 2

Takes all the fields from Query 2, and calculates AvgTransSav, AvgIDSav,
AvgTransPerID.

Now I have a report that uses Query 3 for it's data.

I have a form that launches the report.

Can I specify beginning and end paid dates (limiting criteria in Query 1,
but not displayed) as part of the WHERE statement on the
"DoCmd.OpenReport"
command?

I already know it won't automatically go back through the food chain of
queries and apply it to the first (or if it does, I've screwed something
else
wrong).

Is there a way to specify, in the WHERE statement, that I want that
criterial applied to Query 1?

 

[Allen Browne]

There are several issues here. The most crucial is that you cannot compare
anything to Null, i.e. you must use:
Is Null
not:
=Null

The next issue is that Between is too clumsy in this context. Better to
seprate it into 2 comparisons. An added benefit of doing this is that you
can search for "less than the day after the end date" so that if your
date/time field does have a time component the last date is included.

The third issue is that you can actually get your WHERE clause to return
True for the comparsion if the text box is Null, so that the condition is
effectively not applied.

Switch the query to SQL View (View menu), and change the WHERE clause so it
looks like this:

WHERE ((Forms]![frmrptNetwSav]![RepStartDate] Is Null)
OR ([Table1].[Date1] >= Forms]![frmrptNetwSav]![RepStartDate]))
AND (([Forms]![frmrptNetwSav]![RepEndDate] Is Null)
OR ([Table1].[Date1] < [Forms]![frmrptNetwSav]![RepEndDate] + 1))

Naturally, replace Table1.Date1 with your actual date field.

If all of that is still not adequate, you still have the option of creating
the whole SQL statement, and assigning it to the SQL property of the
QueryDef before you open the report. This approach means the query has the
literal dates in it, even if the user does manage to close the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes, I have been having the base-level query pull the dates from the form,
and that's been working fine. There're no problems with that, but a
couple
of comments -

Several other advice-givers on other threads have expounded on the evils
of
pulling criteria from a form, because the form has continue to be open,
etc.
etc. Not an issue here, because the only way the end-user can get to the
report is through this form, and the only way to navigate back out is
through
the form, so there's no worries about trying to pull the report without
the
form.

What I was really hoping to do is to idiot-proof....err, end-user proof
the
form in case of "null" values. I wanted to program in that if null values
were present in both beginning and end fields, it would pass no "where"
criteria, basically making the report all-inclusive.

When I try putting that in at the query level, I get no records returned.

IIf([Forms]![frmrptNetwSav]![RepStartDate]=Null And
[Forms]![frmrptNetwSav]![RepEndDate]=Null,Is Not Null,Between
[Forms]![frmrptNetwSav]![RepStartDate] And
[Forms]![frmrptNetwSav]![RepEndDate])

Same problems if I tried "Is Null" instead of "= Null", and if my output
to
the criteria field is put in quotes or not.

As you found, you can only apply a WhereCondition on fields that are in
the
report. Since the fields from your lowest level query are not in the
report,
you need a different approach.

A simple approach is to have Query1 read the dates from a form. Assuming
a
form named frmWhatDates with text boxes named txtStartDate and
txtEndDate,
you would put this in the Criteria row of Query1 under the PdDate field:
Between [Forms].[frmWhatDates].[txtStartDate] And
[Forms].[frmWhatDates].[txtEndDate]

Set the Format property of both text boxes to Short Date so they accept
only
valid dates. Add a command button to the form to OpenReport. When it
opens,
the lowest level query reads the dates from the form, and you're home.

If that is not suitable, other possibilities involve using subqueries
instead of stacked queries, or actually altering the SQL property of the
QueryDef before you OpenReport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table of transaction data.

I'm creating the report using the following steps -

Query1 - from the transaction table

Pulls "Group"(Group), "ID"(Group), "Trans#"(count), "Pd Amount"(Sum),
"Req
Amount"(Sum) and calculates "Savings"(Sum) WHERE PdDate Is Not Null

Query 2 - from Query 1

Groups by "Group", "ID"(Count), "Trans#(count)"(Sum), "Pd Amount"(Sum),
"Req
Amount"(Sum), "Savings"(sum)

Query 3 - from Query 2

Takes all the fields from Query 2, and calculates AvgTransSav,
AvgIDSav,
AvgTransPerID.

Now I have a report that uses Query 3 for it's data.

I have a form that launches the report.

Can I specify beginning and end paid dates (limiting criteria in Query
1,
but not displayed) as part of the WHERE statement on the
"DoCmd.OpenReport"
command?

I already know it won't automatically go back through the food chain of
queries and apply it to the first (or if it does, I've screwed
something
else
wrong).

Is there a way to specify, in the WHERE statement, that I want that
criterial applied to Query 1?

 

[aemAndy]

Except that sometimes when I try that, I get a VBA error that says "Object
Required"

There are several issues here. The most crucial is that you cannot compare
anything to Null, i.e. you must use:
Is Null
not:
=Null

The next issue is that Between is too clumsy in this context. Better to
seprate it into 2 comparisons. An added benefit of doing this is that you
can search for "less than the day after the end date" so that if your
date/time field does have a time component the last date is included.

The third issue is that you can actually get your WHERE clause to return
True for the comparsion if the text box is Null, so that the condition is
effectively not applied.

Switch the query to SQL View (View menu), and change the WHERE clause so it
looks like this:

WHERE ((Forms]![frmrptNetwSav]![RepStartDate] Is Null)
OR ([Table1].[Date1] >= Forms]![frmrptNetwSav]![RepStartDate]))
AND (([Forms]![frmrptNetwSav]![RepEndDate] Is Null)
OR ([Table1].[Date1] < [Forms]![frmrptNetwSav]![RepEndDate] + 1))

Naturally, replace Table1.Date1 with your actual date field.

If all of that is still not adequate, you still have the option of creating
the whole SQL statement, and assigning it to the SQL property of the
QueryDef before you open the report. This approach means the query has the
literal dates in it, even if the user does manage to close the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes, I have been having the base-level query pull the dates from the form,
and that's been working fine. There're no problems with that, but a
couple
of comments -

Several other advice-givers on other threads have expounded on the evils
of
pulling criteria from a form, because the form has continue to be open,
etc.
etc. Not an issue here, because the only way the end-user can get to the
report is through this form, and the only way to navigate back out is
through
the form, so there's no worries about trying to pull the report without
the
form.

What I was really hoping to do is to idiot-proof....err, end-user proof
the
form in case of "null" values. I wanted to program in that if null values
were present in both beginning and end fields, it would pass no "where"
criteria, basically making the report all-inclusive.

When I try putting that in at the query level, I get no records returned.

IIf([Forms]![frmrptNetwSav]![RepStartDate]=Null And
[Forms]![frmrptNetwSav]![RepEndDate]=Null,Is Not Null,Between
[Forms]![frmrptNetwSav]![RepStartDate] And
[Forms]![frmrptNetwSav]![RepEndDate])

Same problems if I tried "Is Null" instead of "= Null", and if my output
to
the criteria field is put in quotes or not.

As you found, you can only apply a WhereCondition on fields that are in
the
report. Since the fields from your lowest level query are not in the
report,
you need a different approach.

A simple approach is to have Query1 read the dates from a form. Assuming
a
form named frmWhatDates with text boxes named txtStartDate and
txtEndDate,
you would put this in the Criteria row of Query1 under the PdDate field:
Between [Forms].[frmWhatDates].[txtStartDate] And
[Forms].[frmWhatDates].[txtEndDate]

Set the Format property of both text boxes to Short Date so they accept
only
valid dates. Add a command button to the form to OpenReport. When it
opens,
the lowest level query reads the dates from the form, and you're home.

If that is not suitable, other possibilities involve using subqueries
instead of stacked queries, or actually altering the SQL property of the
QueryDef before you OpenReport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table of transaction data.

I'm creating the report using the following steps -

Query1 - from the transaction table

Pulls "Group"(Group), "ID"(Group), "Trans#"(count), "Pd Amount"(Sum),
"Req
Amount"(Sum) and calculates "Savings"(Sum) WHERE PdDate Is Not Null

Query 2 - from Query 1

Groups by "Group", "ID"(Count), "Trans#(count)"(Sum), "Pd Amount"(Sum),
"Req
Amount"(Sum), "Savings"(sum)

Query 3 - from Query 2

Takes all the fields from Query 2, and calculates AvgTransSav,
AvgIDSav,
AvgTransPerID.

Now I have a report that uses Query 3 for it's data.

I have a form that launches the report.

Can I specify beginning and end paid dates (limiting criteria in Query
1,
but not displayed) as part of the WHERE statement on the
"DoCmd.OpenReport"
command?

I already know it won't automatically go back through the food chain of
queries and apply it to the first (or if it does, I've screwed
something
else
wrong).

Is there a way to specify, in the WHERE statement, that I want that
criterial applied to Query 1?

 

[aemAndy]

I've tried it with both "Is Null" and "= Null".

The reason why I did "=Null" is that "Is Null" would create problems in VBA
expressions, because there, oh so intuitively, you have to say If
IsNull(variableX) instead of "If variableX Is Null".

It seems somewhat idiotic that in the "expression builder" would operate one
way and the language syntax would work another in MS VBA. In any case,
trying to test those fields for nulls, using "Is Null" kills the query.

I'm sticking with forms validation and instructing them to enter an
all-inclusive date range if they want that for their result.

Thanks for all your help and suggestions

There are several issues here. The most crucial is that you cannot compare
anything to Null, i.e. you must use:
Is Null
not:
=Null

The next issue is that Between is too clumsy in this context. Better to
seprate it into 2 comparisons. An added benefit of doing this is that you
can search for "less than the day after the end date" so that if your
date/time field does have a time component the last date is included.

The third issue is that you can actually get your WHERE clause to return
True for the comparsion if the text box is Null, so that the condition is
effectively not applied.

Switch the query to SQL View (View menu), and change the WHERE clause so it
looks like this:

WHERE ((Forms]![frmrptNetwSav]![RepStartDate] Is Null)
OR ([Table1].[Date1] >= Forms]![frmrptNetwSav]![RepStartDate]))
AND (([Forms]![frmrptNetwSav]![RepEndDate] Is Null)
OR ([Table1].[Date1] < [Forms]![frmrptNetwSav]![RepEndDate] + 1))

Naturally, replace Table1.Date1 with your actual date field.

If all of that is still not adequate, you still have the option of creating
the whole SQL statement, and assigning it to the SQL property of the
QueryDef before you open the report. This approach means the query has the
literal dates in it, even if the user does manage to close the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes, I have been having the base-level query pull the dates from the form,
and that's been working fine. There're no problems with that, but a
couple
of comments -

Several other advice-givers on other threads have expounded on the evils
of
pulling criteria from a form, because the form has continue to be open,
etc.
etc. Not an issue here, because the only way the end-user can get to the
report is through this form, and the only way to navigate back out is
through
the form, so there's no worries about trying to pull the report without
the
form.

What I was really hoping to do is to idiot-proof....err, end-user proof
the
form in case of "null" values. I wanted to program in that if null values
were present in both beginning and end fields, it would pass no "where"
criteria, basically making the report all-inclusive.

When I try putting that in at the query level, I get no records returned.

IIf([Forms]![frmrptNetwSav]![RepStartDate]=Null And
[Forms]![frmrptNetwSav]![RepEndDate]=Null,Is Not Null,Between
[Forms]![frmrptNetwSav]![RepStartDate] And
[Forms]![frmrptNetwSav]![RepEndDate])

Same problems if I tried "Is Null" instead of "= Null", and if my output
to
the criteria field is put in quotes or not.

As you found, you can only apply a WhereCondition on fields that are in
the
report. Since the fields from your lowest level query are not in the
report,
you need a different approach.

A simple approach is to have Query1 read the dates from a form. Assuming
a
form named frmWhatDates with text boxes named txtStartDate and
txtEndDate,
you would put this in the Criteria row of Query1 under the PdDate field:
Between [Forms].[frmWhatDates].[txtStartDate] And
[Forms].[frmWhatDates].[txtEndDate]

Set the Format property of both text boxes to Short Date so they accept
only
valid dates. Add a command button to the form to OpenReport. When it
opens,
the lowest level query reads the dates from the form, and you're home.

If that is not suitable, other possibilities involve using subqueries
instead of stacked queries, or actually altering the SQL property of the
QueryDef before you OpenReport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table of transaction data.

I'm creating the report using the following steps -

Query1 - from the transaction table

Pulls "Group"(Group), "ID"(Group), "Trans#"(count), "Pd Amount"(Sum),
"Req
Amount"(Sum) and calculates "Savings"(Sum) WHERE PdDate Is Not Null

Query 2 - from Query 1

Groups by "Group", "ID"(Count), "Trans#(count)"(Sum), "Pd Amount"(Sum),
"Req
Amount"(Sum), "Savings"(sum)

Query 3 - from Query 2

Takes all the fields from Query 2, and calculates AvgTransSav,
AvgIDSav,
AvgTransPerID.

Now I have a report that uses Query 3 for it's data.

I have a form that launches the report.

Can I specify beginning and end paid dates (limiting criteria in Query
1,
but not displayed) as part of the WHERE statement on the
"DoCmd.OpenReport"
command?

I already know it won't automatically go back through the food chain of
queries and apply it to the first (or if it does, I've screwed
something
else
wrong).

Is there a way to specify, in the WHERE statement, that I want that
criterial applied to Query 1?

 

[Allen Browne]

Yes, the expression
Is Null
works in queries (and in expressions that are evaluated as queries, such as
the criteria of DLookup().)

In VBA code, you need to use the IsNull() function, e.g.:
If IsNull([SomeTextBox]) Then ...