Invalid Use Of Null (1 Viewer)

O

Oli_Oli_Umphry

Guest
I have a form that runs a simple macro. The problem is I'm getting the "Invalid Use Of Null" error becuase some of the fields are empty. There are 12 fields....one for each month. I know there's a way to use the nz function but I do not know how and where to apply it for each field.

example:
quantity month total price
10 jan: $3548.00
feb:
10 mar: $3548.00

The Macro calculates the totals for the year.

Thanks in advance
 

godofhell

Database Guru
Local time
Today, 14:11
Joined
May 20, 2005
Messages
180
you simply put it to check against the filed you want to check in VB code.

If nz(mytextbox) Then
Do Something
End If

It is the same if checking for Null\
if isNull(mytextbox) Then
Do Something
End If
 

MarkK

bit cruncher
Local time
Today, 14:11
Joined
Mar 17, 2004
Messages
8,178
Guru's advice is incorrect in respect to the Nz function. Search VBA help on "Nz Function" for accurate information on what it does and the type of value it returns.
 

godofhell

Database Guru
Local time
Today, 14:11
Joined
May 20, 2005
Messages
180
Lagbolt, you are correct. I did provide incorrect information on the use of nz. Here is the correct information on its use. Thank you for keeping me honest.

You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

Syntax

Nz(variant[, valueifnull])

The Nz function has the following arguments.

Argument Description
variant A variable of data type Variant.
valueifnull Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string.
Note If you use the Nz function in an expression in a query without using the valueifnull argument, the results will be a zero-length string in the fields that contain null values.

In the next example, the optional argument supplied to the Nz function provides the string to be returned if varFreight is Null.

varResult = Nz(varFreight, "No Freight Charge")
 
R

Rich

Guest
Oli your problem is that you're re-creating a spreadsheet in Access. You don't need separate fields for each month. Just a couple will do, in simple terms one for the amount and one to define which month the figure relates to.
If you set the fields up correctly then it's just a simple Sum to get the totals instead of =Month1+Month2+Month3 etc. etc.
 

Users who are viewing this thread

Top Bottom