#Error

[Randal]

How can I get a query to return "0" instead of "#Error"?

 

[Allen Browne]

Best approach would be to understand why it returns #Error.

Could it be a "Division by zero" error, if you are dividing one field by
another?

Is this a calculated field, with a function call that has bad arguments?
For example, if you use:
DLookup("MyField", "MyTable", "SomeField = " & [XXX])
and XXX is null, the 3rd argument is malformed. Or if you use:
DateSerial(Year(Date()), Month([DOB]), Day([DOB]))
where DOB is null, the assignment fails.

It is possible to work around the error with IsError() and IIf(), but it
would be instructive to figure out what's causing it.

 

[Randal]

Thanks. It is division by 0. But, I don't want to change the formula
because it works in 99% of the cases.

Best approach would be to understand why it returns #Error.

Could it be a "Division by zero" error, if you are dividing one field by
another?

Is this a calculated field, with a function call that has bad arguments?
For example, if you use:
DLookup("MyField", "MyTable", "SomeField = " & [XXX])
and XXX is null, the 3rd argument is malformed. Or if you use:
DateSerial(Year(Date()), Month([DOB]), Day([DOB]))
where DOB is null, the assignment fails.

It is possible to work around the error with IsError() and IIf(), but it
would be instructive to figure out what's causing it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I get a query to return "0" instead of "#Error"?

 

[Allen Browne]

You have a simple choice: correct the formula, or put up with the error.

It is not difficult to fix, e.g.:
=IIf([denom] = 0, 0, [num] / denom])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. It is division by 0. But, I don't want to change the formula
because it works in 99% of the cases.

Best approach would be to understand why it returns #Error.

Could it be a "Division by zero" error, if you are dividing one field by
another?

Is this a calculated field, with a function call that has bad arguments?
For example, if you use:
DLookup("MyField", "MyTable", "SomeField = " & [XXX])
and XXX is null, the 3rd argument is malformed. Or if you use:
DateSerial(Year(Date()), Month([DOB]), Day([DOB]))
where DOB is null, the assignment fails.

It is possible to work around the error with IsError() and IIf(), but it
would be instructive to figure out what's causing it.

How can I get a query to return "0" instead of "#Error"?

 

[Randal]

That was an easy correction. Thank you.

You have a simple choice: correct the formula, or put up with the error.

It is not difficult to fix, e.g.:
=IIf([denom] = 0, 0, [num] / denom])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. It is division by 0. But, I don't want to change the formula
because it works in 99% of the cases.

Best approach would be to understand why it returns #Error.

Could it be a "Division by zero" error, if you are dividing one field by
another?

Is this a calculated field, with a function call that has bad arguments?
For example, if you use:
DLookup("MyField", "MyTable", "SomeField = " & [XXX])
and XXX is null, the 3rd argument is malformed. Or if you use:
DateSerial(Year(Date()), Month([DOB]), Day([DOB]))
where DOB is null, the assignment fails.

It is possible to work around the error with IsError() and IIf(), but it
would be instructive to figure out what's causing it.

"Randal" <rlwnrAThighstream.net> wrote in message
How can I get a query to return "0" instead of "#Error"?