Null value to "0" / Currency format

[Slez via AccessMonster.com]

I have a crosstab query in which my columns, based on [BidType], give totals
based on the field LineTotalSellPrice. It returns the totals properly with
the expression:

LineTotalSellPrice: Sum(([Quantity]*([UnitPrice]+[QuoteCost]))*[Markup])

...however in the instances where nothing exists, it returns Null, or blank.
I'd like to convert this to a currency value of $0.00, so I changed my
expression to:

LineTotalSellPrice: Nz(Sum(([Quantity]*([UnitPrice]+[QuoteCost]))*[Markup]),0)


...and it returns the proper value, but not in a currency format (even though
the field's format property is set to Currency). On the report that is based
off of this query, it won't return the data in currency format even though
that is what is chosen in the control's properties.

It seems to me that I need to get the query to return in currency for the
report to work properly. What can be done to correct this?
Thank you!

 

[Ofer Cohen]

Try using Format

LineTotalSellPrice:
Format(Nz(Sum(([Quantity]*([UnitPrice]+[QuoteCost]))*[Markup]),0),"Currency")

--
Good Luck
BS"D

I have a crosstab query in which my columns, based on [BidType], give totals
based on the field LineTotalSellPrice. It returns the totals properly with
the expression:

LineTotalSellPrice: Sum(([Quantity]*([UnitPrice]+[QuoteCost]))*[Markup])

...however in the instances where nothing exists, it returns Null, or blank.
I'd like to convert this to a currency value of $0.00, so I changed my
expression to:

LineTotalSellPrice: Nz(Sum(([Quantity]*([UnitPrice]+[QuoteCost]))*[Markup]),0)


...and it returns the proper value, but not in a currency format (even though
the field's format property is set to Currency). On the report that is based
off of this query, it won't return the data in currency format even though
that is what is chosen in the control's properties.

It seems to me that I need to get the query to return in currency for the
report to work properly. What can be done to correct this?
Thank you!

 

[Slez via AccessMonster.com]

That works perfectly!
Thanks for the help!
Slez

Try using Format

LineTotalSellPrice:
Format(Nz(Sum(([Quantity]*([UnitPrice]+[QuoteCost]))*[Markup]),0),"Currency")

I have a crosstab query in which my columns, based on [BidType], give totals
based on the field LineTotalSellPrice. It returns the totals properly with

[quoted text clipped - 16 lines]

report to work properly. What can be done to correct this?
Thank you!