calculations in report controls

[jmoore]

Everything was working fine using a calculated field in a query and control
source in a report text box. The calculated field [RefBeforeOpen] in the
query uses the DateDiff function to determine the number of days between two
date fields. In the control source of a text box in the report footer, the
average number of days is obtained using
=Sum([RefBeforeOpen])/Count([RefBeforeOpen]).

But now we have a run into a situation where one of the date fields is not
available. The revised calculated query field is RefBeforeOpen:
IIf([F1]=#1/1/7777#,"7777", DateDiff("d",[F1],[F3])). We are using the date
1/1/7777 to indicate not available. This gives us the correct number of days
between the two dates, or displays 7777 if one date is missing. The average
number of days (in the report) should sum and count only those records less
than 7777. This is where I am having trouble. I tried
=Sum([RefBeforeOpen]<"7777")/Count([RefBeforeOpen]<"7777"), which did not
give the correct results.

I also tried to get around it by creating hidden text boxes that would give
me the sum of records less than and more than 7777, but again failed to get
the correct answer. The =Sum([RefBeforeOpen]="7777") actually produces a
count of negative one (there is only one instance so far with a missing date).

Thanks for helping me solve this problem.

 

[Duane Hookom]

I don't care for any expression that might return a string or a number. It
should be one or the other (or Null). In your expression, 7777 is a string
while DateDiff() returns a numeric. I would try:
IIf([F1]=#1/1/7777#,Null, DateDiff("d",[F1],[F3]))
If you want to display the Null as 7777, you can simply set the Format
property of your text box.
Then, you don't have to do anything special to display the count, sum, or
average of the value.

 

[Duane Hookom]

I don't care for any expression that might return a string or a number. It
should be one or the other (or Null). In your expression, 7777 is a string
while DateDiff() returns a numeric. I would try:
IIf([F1]=#1/1/7777#,Null, DateDiff("d",[F1],[F3]))
If you want to display the Null as 7777, you can simply set the Format
property of your text box.
Then, you don't have to do anything special to display the count, sum, or
average of the value.

 

[jmoore]

Thank you VERY much. It works like a charm now.

I thought the 7777 in my original expression was numeric. For future use,
is there a way to ensure the expression returns a numeric value?

I don't care for any expression that might return a string or a number. It
should be one or the other (or Null). In your expression, 7777 is a string
while DateDiff() returns a numeric. I would try:
IIf([F1]=#1/1/7777#,Null, DateDiff("d",[F1],[F3]))
If you want to display the Null as 7777, you can simply set the Format
property of your text box.
Then, you don't have to do anything special to display the count, sum, or
average of the value.

--
Duane Hookom
Microsoft Access MVP

Everything was working fine using a calculated field in a query and control
source in a report text box. The calculated field [RefBeforeOpen] in the
query uses the DateDiff function to determine the number of days between two
date fields. In the control source of a text box in the report footer, the
average number of days is obtained using
=Sum([RefBeforeOpen])/Count([RefBeforeOpen]).

But now we have a run into a situation where one of the date fields is not
available. The revised calculated query field is RefBeforeOpen:
IIf([F1]=#1/1/7777#,"7777", DateDiff("d",[F1],[F3])). We are using the date
1/1/7777 to indicate not available. This gives us the correct number of days
between the two dates, or displays 7777 if one date is missing. The average
number of days (in the report) should sum and count only those records less
than 7777. This is where I am having trouble. I tried
=Sum([RefBeforeOpen]<"7777")/Count([RefBeforeOpen]<"7777"), which did not
give the correct results.

I also tried to get around it by creating hidden text boxes that would give
me the sum of records less than and more than 7777, but again failed to get
the correct answer. The =Sum([RefBeforeOpen]="7777") actually produces a
count of negative one (there is only one instance so far with a missing date).

Thanks for helping me solve this problem.

 

[jmoore]

Thank you VERY much. It works like a charm now.

I thought the 7777 in my original expression was numeric. For future use,
is there a way to ensure the expression returns a numeric value?

I don't care for any expression that might return a string or a number. It
should be one or the other (or Null). In your expression, 7777 is a string
while DateDiff() returns a numeric. I would try:
IIf([F1]=#1/1/7777#,Null, DateDiff("d",[F1],[F3]))
If you want to display the Null as 7777, you can simply set the Format
property of your text box.
Then, you don't have to do anything special to display the count, sum, or
average of the value.

--
Duane Hookom
Microsoft Access MVP

Everything was working fine using a calculated field in a query and control
source in a report text box. The calculated field [RefBeforeOpen] in the
query uses the DateDiff function to determine the number of days between two
date fields. In the control source of a text box in the report footer, the
average number of days is obtained using
=Sum([RefBeforeOpen])/Count([RefBeforeOpen]).

But now we have a run into a situation where one of the date fields is not
available. The revised calculated query field is RefBeforeOpen:
IIf([F1]=#1/1/7777#,"7777", DateDiff("d",[F1],[F3])). We are using the date
1/1/7777 to indicate not available. This gives us the correct number of days
between the two dates, or displays 7777 if one date is missing. The average
number of days (in the report) should sum and count only those records less
than 7777. This is where I am having trouble. I tried
=Sum([RefBeforeOpen]<"7777")/Count([RefBeforeOpen]<"7777"), which did not
give the correct results.

I also tried to get around it by creating hidden text boxes that would give
me the sum of records less than and more than 7777, but again failed to get
the correct answer. The =Sum([RefBeforeOpen]="7777") actually produces a
count of negative one (there is only one instance so far with a missing date).

Thanks for helping me solve this problem.