Multiple criteria

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

This seems as if it should work, but it doesn't. I have a
report based on a parameter query. The parameters are a
person's name and a date range. I made a form (frmSelect)
with a combo box [cboName] to select the name. In the
query, in the EmployeeID field, I put [Forms]![frmSelect]!
[cboName]. (The combo box's bound column is EmployeeID,
though the visible column is the name). I placed a
command button on frmSelect to open the report.
The other parameter is the SessionDate field, using
Between And. When I selected the name as just described,
then clicked the button to open the report, I was prompted
for Start Date and End Date. It worked.
Next I added text boxes (txtStartDate and txtEndDate) to
frmSelect. In the Query, I made the parameter Between
[Forms]![frmSelect]![txtStartDate] And [Forms]![frmSelect]!
[txtEndDate]. However, I am still prompted for the Start
Date and End Date, even after filling in the text boxes.
It doesn't seem to make any difference what date format I
use in the table that is the query's source, but in case
it matters the date format in the table is mmm d", "yyyy.
Even when I changed the table field to short date and made
the format of txtStartDate and txtEndDate short date the
same thing happened. Any ideas as to what I have
overlooked?
 
Bruce said:
This seems as if it should work, but it doesn't. I have a
report based on a parameter query. The parameters are a
person's name and a date range. I made a form (frmSelect)
with a combo box [cboName] to select the name. In the
query, in the EmployeeID field, I put [Forms]![frmSelect]!
[cboName]. (The combo box's bound column is EmployeeID,
though the visible column is the name). I placed a
command button on frmSelect to open the report.
The other parameter is the SessionDate field, using
Between And. When I selected the name as just described,
then clicked the button to open the report, I was prompted
for Start Date and End Date. It worked.
Next I added text boxes (txtStartDate and txtEndDate) to
frmSelect. In the Query, I made the parameter Between
[Forms]![frmSelect]![txtStartDate] And [Forms]![frmSelect]!
[txtEndDate]. However, I am still prompted for the Start
Date and End Date, even after filling in the text boxes.
It doesn't seem to make any difference what date format I
use in the table that is the query's source, but in case
it matters the date format in the table is mmm d", "yyyy.
Even when I changed the table field to short date and made
the format of txtStartDate and txtEndDate short date the
same thing happened. Any ideas as to what I have
overlooked?
Click to expand...

Check the report for any use of [Start Date], probably in a
text box in the report's header section.

Also check the query to make sure it doesn't use the
parameters anywhere else.
 
Thanks, I finally found it in an expression in a text box
in the group header. I also removed some fields from the
report's underlying query, since I had replaced them with
concatenated fields in the query. However, I had not
removed them from the sorting and grouping list. That
took a while to find. At least I learned something from
all this. I wonder what it was?
-----Original Message-----
Bruce said:
This seems as if it should work, but it doesn't. I have a
report based on a parameter query. The parameters are a
person's name and a date range. I made a form (frmSelect)
with a combo box [cboName] to select the name. In the
query, in the EmployeeID field, I put [Forms]! [frmSelect]!
[cboName]. (The combo box's bound column is EmployeeID,
though the visible column is the name). I placed a
command button on frmSelect to open the report.
The other parameter is the SessionDate field, using
Between And. When I selected the name as just described,
then clicked the button to open the report, I was prompted
for Start Date and End Date. It worked.
Next I added text boxes (txtStartDate and txtEndDate) to
frmSelect. In the Query, I made the parameter Between
[Forms]![frmSelect]![txtStartDate] And [Forms]! [frmSelect]!
[txtEndDate]. However, I am still prompted for the Start
Date and End Date, even after filling in the text boxes.
It doesn't seem to make any difference what date format I
use in the table that is the query's source, but in case
it matters the date format in the table is mmm d", "yyyy.
Even when I changed the table field to short date and made
the format of txtStartDate and txtEndDate short date the
same thing happened. Any ideas as to what I have
overlooked?
Click to expand...

Check the report for any use of [Start Date], probably in a
text box in the report's header section.

Also check the query to make sure it doesn't use the
parameters anywhere else.
Click to expand...
 
That's an excellent question ;-)
Too many folks just get it to work and never wonder what the
underlying issues are.

Let me try to explain why you got a prompt instead of an
error for the text box and grouping expressions, it's
actually a pretty cool feature of Access reports.

An undefined name in a report control's expressions is
treated the same as a parameter in the report's record
source query. This allows you to do something like use a
text box to prompt for a value (such as a starting page
number) that would be used in report formatting or
calculations.

Kind of the reverse of that also works very nicely. A
parameter in the report's record source query can be
referred to in an expression the same as if it were a field
in the query. This is extremely useful if you want the
report's header to display the parameter values. For
example, if the query prompts for start and end dates by
using a criteria like Between [Start Date] And [End Date]
then you can very simply use a text box expression:
="Sales from " & [Start Date] & " to " & [End Date]
to display the range of dates that are covered in the
report.
--
Marsh
MVP [MS Access]


Thanks, I finally found it in an expression in a text box
in the group header. I also removed some fields from the
report's underlying query, since I had replaced them with
concatenated fields in the query. However, I had not
removed them from the sorting and grouping list. That
took a while to find. At least I learned something from
all this. I wonder what it was?
-----Original Message-----
Bruce said:
This seems as if it should work, but it doesn't. I have a
report based on a parameter query. The parameters are a
person's name and a date range. I made a form (frmSelect)
with a combo box [cboName] to select the name. In the
query, in the EmployeeID field, I put [Forms]! [frmSelect]!
[cboName]. (The combo box's bound column is EmployeeID,
though the visible column is the name). I placed a
command button on frmSelect to open the report.
The other parameter is the SessionDate field, using
Between And. When I selected the name as just described,
then clicked the button to open the report, I was prompted
for Start Date and End Date. It worked.
Next I added text boxes (txtStartDate and txtEndDate) to
frmSelect. In the Query, I made the parameter Between
[Forms]![frmSelect]![txtStartDate] And [Forms]! [frmSelect]!
[txtEndDate]. However, I am still prompted for the Start
Date and End Date, even after filling in the text boxes.
It doesn't seem to make any difference what date format I
use in the table that is the query's source, but in case
it matters the date format in the table is mmm d", "yyyy.
Even when I changed the table field to short date and made
the format of txtStartDate and txtEndDate short date the
same thing happened. Any ideas as to what I have
overlooked?
Click to expand...
Click to expand...
Marshall said:
Check the report for any use of [Start Date], probably in a
text box in the report's header section.

Also check the query to make sure it doesn't use the
parameters anywhere else.
Click to expand...
Click to expand...
 
Back
Top