Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-19-2005, 04:56 AM   #1
Oli_Oli_Umphry
Guest
 
Posts: n/a
Invalid Use Of Null

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

  Reply With Quote
Old 06-19-2005, 02:25 PM   #2
godofhell
Database Guru
 
Join Date: May 2005
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
godofhell is on a distinguished road
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
__________________
Godofhell
www.kavalpewter.com
godofhell is offline   Reply With Quote
Old 06-20-2005, 03:14 PM   #3
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 4,122
Thanks: 3
Thanked 404 Times in 387 Posts
MarkK is just really nice MarkK is just really nice MarkK is just really nice MarkK is just really nice
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.

__________________
formerly known as lagbolt | Windows 7 | Access 2007 | Visual Studio 2008 |
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MarkK is offline   Reply With Quote
Old 06-20-2005, 04:38 PM   #4
godofhell
Database Guru
 
Join Date: May 2005
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
godofhell is on a distinguished road
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")
__________________
Godofhell
www.kavalpewter.com
godofhell is offline   Reply With Quote
Old 06-20-2005, 11:28 PM   #5
Rich
Guest
 
Posts: n/a
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.

  Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Procedure too large, what can I do?? wh00t Modules & VBA 16 02-18-2013 07:48 AM
invalid use of null pbuethe Modules & VBA 8 07-30-2003 11:44 PM
I don't get it RobJ Forms 4 03-23-2003 10:47 AM




All times are GMT -8. The time now is 03:21 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
(c) copyright 2010 Access World