R
Randal
How can I get a query to return "0" instead of "#Error"?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
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.
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.
R
Randal
Thanks. It is division by 0. But, I don't want to change the formula
because it works in 99% of the cases.
because it works in 99% of the cases.
Allen Browne said: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.
Randal said:How can I get a query to return "0" instead of "#Error"?
A
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.
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.
Randal said:Thanks. It is division by 0. But, I don't want to change the formula
because it works in 99% of the cases.
Allen Browne said: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 said:How can I get a query to return "0" instead of "#Error"?
R
Randal
That was an easy correction. Thank you.
Allen Browne said: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.
Randal said:Thanks. It is division by 0. But, I don't want to change the formula
because it works in 99% of the cases.
Allen Browne said: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"?