#NUM! 1

[mcongdon]

Is there any way I can get this to display as a blank when it is caused by a divide by zero error? The queries the field is calculated from are not always populated with data.

Here is the code for the query:

SELECT Sum([Execution Price]) AS MUNIExecutionBuy, Sum([Street Price]) AS MUNIStreetBuy, Sum([Quantity]) AS MUNIQuantityBuy, Sum(([Execution Price]-[Street Price])*[Quantity]*10) AS MUNIConcessionBuy, Count([Execution Price]) AS MUNITicketCountBuy
FROM tbl_BLOTTER
WHERE ((([tbl_BLOTTER].[Type])="Municipal") And (([tbl_BLOTTER].[Trade Date])=[Forms]![frm_DAILYBLOTDATE]![cboStartDate]) And (([tbl_BLOTTER].[Firm])=0) And (([tbl_BLOTTER].[Buy/Sell])="Buy"));

When the field on the report is just displaying what is calculated in the query, there is no problem. When I try to create a text box on the report which makes simple calculations (multiply or divide by 100) from the other blank text boxes on the report, I get a #NUM! error.
Any ideas on where to start?

 

[lameid]

I am not sure which field you are trying to do math on but using the IIF function and Isnull functions will probably work for you. The below is for fixing divide by zero. It is null or blank if the denominator is null. You might also considering using NZ function and testing if it is equal to zero. It all depends on what exactly you are trying to fix or leave as an exception.

Example control source

=IIF(Isnull(DenominatorField), Null, NumeratorField/DenominatorField)

 

[dhookom]

The expression suggested by lameid would need to be modified to account for DenominatorField = 0.

=IIF(Nz(DenominatorField,0)=0, Null, NumeratorField/DenominatorField)

Duane
Hook'D on Access
MS Access MVP

 

[mcongdon]

Thanks Guys, that worked perfect!