#Error when record is not found in query

[CW]

My qryAllRecords includes all order records and shows a variety of data. One
of the fields is InvoiceValue.
The source for that field is another query (qryOrdersInvoiced) which lists
all orders that have been invoiced.
When qryAllRecords includes an order that hasn't been invoiced yet, the
InvoiceValue field displays #Error because it hasn't found anything in
qryOrdersInvoiced.
I can understand this, but would like it to show 0.00 rather than #Error.
How can I achieve this?
Many thanks
CW

 

[golfinray]

In a new column, put IIF([qryordersinvoiced]is null,"0.00",[qryordersinvoiced])

 

[CW]

Thanks, but surely I need to refer to the fieldname, not to the query that
contains that field??

In a new column, put IIF([qryordersinvoiced]is null,"0.00",[qryordersinvoiced])

My qryAllRecords includes all order records and shows a variety of data. One
of the fields is InvoiceValue.
The source for that field is another query (qryOrdersInvoiced) which lists
all orders that have been invoiced.
When qryAllRecords includes an order that hasn't been invoiced yet, the
InvoiceValue field displays #Error because it hasn't found anything in
qryOrdersInvoiced.
I can understand this, but would like it to show 0.00 rather than #Error.
How can I achieve this?
Many thanks
CW

 

[Tom Wickerath]

Hi CW,

Thanks, but surely I need to refer to the fieldname, not to the query that
contains that field??

Yep. From your first message:

When qryAllRecords includes an order that hasn't been invoiced yet, the
InvoiceValue field displays #Error because it hasn't found anything in
qryOrdersInvoiced.

So, try this:

InvoicedValue: IIF(IsNull([InvoiceValue]), 0, [InvoiceValue])

Notes:
1.) I have used a slightly different alias name, InvoicedValue, to avoid a
circular reference.
2.) This field will not be updatable, as it is based on an expression.

You can also use the Nz function, but that will return a string value:

InvoicedValue: Nz([InvoiceValue],0)

You can wrap the results of the above in a type conversion function. For
example, using the CCur function:

InvoicedValue: CCur(Nz([InvoiceValue],0))

While still not updateable, at least you can do math with the second result.

Here is a KB article you might find useful. Disregard the "ACC2000:" in the
title:
(ACC2000 Troubleshooting Tips for Error Values
http://support.microsoft.com/?id=209132


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________