10-27-2011, 02:34 AM #7 PeterF Newly Registered User Join Date: Jun 2006 Posts: https://msdn.microsoft.com/en-us/library/bb148937(v=office.12).aspx Application.Nz Method Office 2007 Access Developer Reference You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null.

Between Intellisense and the VBA Help screens you can't go far off course, especially because Intellisense prompts you for each argument. #func Access Error If that does not resolve the issue, then post the remaining code as I described, and we will see what we can do for you. __________________ No one is expected to That's going to leave your users scratching their heads and reaching for the phone to ask what's up. Last edited by student; 10-27-2011 at 02:18 AM.

Access #error

If the value of the control is Null, the procedure uses the Nz function to convert a Null value to a zero-length string.Visual Basic for ApplicationsPublic Sub CheckValue() Dim frm As http://www.access-programmers.co.uk/forums/showthread.php?t=217455 A Variant that supplies a value to be returned if the variant argument is Null. Iserror Access The user is convinced your program lost them, though they are still there in the table. Ms Access #error In Query For example: In this query, we have used the Nz function as follows: Expr1: Nz([CategoryName],'Not Found') This query will evaluate whether the CategoryName field contains a null value and display the

This argument enables you to return a value other than zero or a zero-length string.   NoteIf you use the Nz function in an expression in a query without using the valueifnull see here Yes No Great! I have tried using IIf(Iserror([AddedMethanol], as well as both IIf(IsNull([AddedMethanol and IsEmpty(). It worked perfectly. Access If Error Then 0

Nz(FieldToTest, ReplaceValue) [/CODE] It was my understanding that the 'ReplaceValue' was an optional field, as per MS: office.microsoft. Is that normal? Of course, the best solution is to prevent missing values in the first place. http://axisnice.com/access-error/access-error-75.php Some items have not been moved in the month so have no record to pull through to this query so the result produces an #Error result as opposed to a blank

There are functions to convert data types, perform calculations on dates, perform simple to complex mathematics, make financial calculations, manage text strings, format values, and retrieve data from tables, among others. Replace #error With 0 In Access Otherwise, it will return the value from the CategoryName field. Consider what he said about Nz().

The issue seems to be that some records called from QUERY_A (or _B) do not exist, while others that are called have a null value for field QTY1 (or 2) I'm

It's entirely new to me! varResult = IIf(Nz(varFreight) > 50, "High", "Low") If you supply a value for the optional argument valueifnull, that value will be returned when variant is Null. Error 1: Nulls in Criteria If you enter criteria under a field in a query, it returns only matching records. Access #num Error Similarly the assignment of the ClientID value to a numeric variable may cause an error.

For example, the expression 2 + varX will always return a Null value when the Variant varX is Null. TIP I don't know of any developer who knows every available function off the top of his or her head, so don't expect or think you need to. All rights reserved. Get More Info Reason: addition student View Public Profile Find More Posts by student 10-27-2011, 01:41 AM #4 mcalex Newly Registered User Join Date: Jun 2009 Posts:

For a typical Invoice table, the line items of the invoice are stored in an InvoiceDetail table, joined to the Invoice table by an InvoiceID. However, 2 + Nz(varX) returns 2.