Can't get SUM to work

[Pam]

I have a report that is giving me fits and there is probably a simple
solution. Query has this expression which returns correct value:

ReceiptOfGoodsToNow:
IIf(IsNull([PriceQuote]),DateDiff("d",[ReceiptOfGoods],Now()),"")

I have put this field from the query on the report "ReceiptOfGoodsToNow". I
have created a text field with the following equation in the detail, group
and report footer sections and cannot get it to return a value.

=Sum([ReceiptOfGoodsToNow])

Now, I get error message "Data mismatch". I had named the field
txtReceiptOfGoodsToNow" and was getting a parameter prompt for the name of
the field.

If anyone has a solution, it is very much appreciated.

Thanks,
Pam

 

[Marshall Barton]

I have a report that is giving me fits and there is probably a simple
solution. Query has this expression which returns correct value:

ReceiptOfGoodsToNow:
IIf(IsNull([PriceQuote]),DateDiff("d",[ReceiptOfGoods],Now()),"")

I have put this field from the query on the report "ReceiptOfGoodsToNow". I
have created a text field with the following equation in the detail, group
and report footer sections and cannot get it to return a value.

=Sum([ReceiptOfGoodsToNow])

Now, I get error message "Data mismatch". I had named the field
txtReceiptOfGoodsToNow" and was getting a parameter prompt for the name of
the field.

Since you are assigning an empty string to the field when
PriceQuote is null, the type of the field is Text and Sum
can not operate on text values.

Try changing the "" to Null or 0

 

[Pam]

Marshall,

Thanks for the prompt reply. I didn't want to see all the 0's in my report
and thought that would work. Null worked perfect.

I do seem to have one other problem. It's not adding properly. I have
checked and rechecked. I've added what prints on the report and added the
column in the query and am off by a count of five. When I add another field
to the calculation, it totals the two together perfectly.
Any suggestions?
Thanks again,
Pam

I have a report that is giving me fits and there is probably a simple
solution. Query has this expression which returns correct value:

ReceiptOfGoodsToNow:
IIf(IsNull([PriceQuote]),DateDiff("d",[ReceiptOfGoods],Now()),"")

I have put this field from the query on the report "ReceiptOfGoodsToNow".
I
have created a text field with the following equation in the detail, group
and report footer sections and cannot get it to return a value.

=Sum([ReceiptOfGoodsToNow])

Now, I get error message "Data mismatch". I had named the field
txtReceiptOfGoodsToNow" and was getting a parameter prompt for the name of
the field.

Since you are assigning an empty string to the field when
PriceQuote is null, the type of the field is Text and Sum
can not operate on text values.

Try changing the "" to Null or 0

 

[Marshall Barton]

The only idea that comes to mind is that you have a
mispercetion of what DateDiff does. This thought is
prompted by your use of Now() instead of Date(). DateDiff
counts the number of boundaries crossed (days in this case).
For example,
DateDiff("d", #11/10/06 23:59#, #11/11/06 00:01#)
is one day. If you need finer resolution that that, you
will have to specify units of whatever resolution you
require.

If that doesn't help, try to restrict your query down to as
few records that demonstrate the problem and post the query
data and the Sum total that is calculated.
--
Marsh
MVP [MS Access]

Thanks for the prompt reply. I didn't want to see all the 0's in my report
and thought that would work. Null worked perfect.

I do seem to have one other problem. It's not adding properly. I have
checked and rechecked. I've added what prints on the report and added the
column in the query and am off by a count of five. When I add another field
to the calculation, it totals the two together perfectly.
Any suggestions?
Thanks again,
Pam

I have a report that is giving me fits and there is probably a simple
solution. Query has this expression which returns correct value:

ReceiptOfGoodsToNow:
IIf(IsNull([PriceQuote]),DateDiff("d",[ReceiptOfGoods],Now()),"")

I have put this field from the query on the report "ReceiptOfGoodsToNow".
I
have created a text field with the following equation in the detail, group
and report footer sections and cannot get it to return a value.

=Sum([ReceiptOfGoodsToNow])

Now, I get error message "Data mismatch". I had named the field
txtReceiptOfGoodsToNow" and was getting a parameter prompt for the name of
the field.

Since you are assigning an empty string to the field when
PriceQuote is null, the type of the field is Text and Sum
can not operate on text values.

Try changing the "" to Null or 0