Max rate in a range of dates

  • Thread starter Thread starter PizzaBoy
  • Start date Start date
P

PizzaBoy

Hello. I have a report based on a query which produces three fields [UNIT],
[DATE], [PRICE]. I have set up the report to group the records by the same
units. I have set up a text box in the group footer [=Max(IIf([DATE] Between
70604 And 80604,[PRICE],"")) ] I am attempting to determine the maximum
price for a unit within the specified time frame. For example I would like
my results in the text box to show the max price out of all the prices for
the date ranges between 7/6/04 and 8/6/04. So far when I run the report the
text box returns nothing in the group summary. What is wrong here? Thanks
for the help.
 
PizzaBoy said:
Hello. I have a report based on a query which produces three fields [UNIT],
[DATE], [PRICE]. I have set up the report to group the records by the same
units. I have set up a text box in the group footer [=Max(IIf([DATE] Between
70604 And 80604,[PRICE],"")) ] I am attempting to determine the maximum
price for a unit within the specified time frame. For example I would like
my results in the text box to show the max price out of all the prices for
the date ranges between 7/6/04 and 8/6/04. So far when I run the report the
text box returns nothing in the group summary. What is wrong here? Thanks
for the help.
Click to expand...

That's not a valid representation of your dates and the
value of the IIF for dates outside the range must be Null.

=Max(IIf([DATE] Between #7/06/04# And #8/06/04#, [PRICE],
Null))
 
Thank you. Sometimes it's the little things that matter...

Marshall Barton said:
PizzaBoy said:
Hello. I have a report based on a query which produces three fields
[UNIT],
[DATE], [PRICE]. I have set up the report to group the records by the same
units. I have set up a text box in the group footer [=Max(IIf([DATE]
Between
70604 And 80604,[PRICE],"")) ] I am attempting to determine the maximum
price for a unit within the specified time frame. For example I would like
my results in the text box to show the max price out of all the prices for
the date ranges between 7/6/04 and 8/6/04. So far when I run the report
the
text box returns nothing in the group summary. What is wrong here? Thanks
for the help.
Click to expand...

That's not a valid representation of your dates and the
value of the IIF for dates outside the range must be Null.

=Max(IIf([DATE] Between #7/06/04# And #8/06/04#, [PRICE],
Null))
Click to expand...
 
Back
Top