Software

Ask something
Discussion Locked

Chang null values to zeroes/Access

0 helpful
7 replies

by skennedy10 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am a beginning Access developer, and I have
taken 1 1/2 courses in VB. I know VB has the
answer to fix a problem, but I have not yet
figured out how to write it into Access.

My problem is that I have written an
application for my son--bookkeeping for his
business. I keep coming up with empty (null)
values, but when I use that field in a
calculation, the calculation won't calculate.
I have found the NZ function, but I can't
seem to get it into Access successfully.

If the null value is in a table, that's OK as
I have figured out to use =0, not just the 0
as the default value,
but when a query created by joining two
queries tries to calculate something, the =0
in the original field does not fix the problem.

I need help with 1. writing the expression
and 2. where to put it!

Help! TIA!

7 replies

by donq

I too write accounting applications in Access and when I share the answer it's going to be so obvious you'tt really feel bad - but don't. We all learn through error.

I set each field's Default value to "0" (zero) in the Table so if there are no entries the "Zero" replaces the Null. Then when I test for a user entry I verify the answer is NOT equal to 0.

Don't set them at the Form level, use the Table to insure every record ALWAYS has a 0. Of course if the User deletes the zero we get the same error? But that is extremely rare and requires a user screw-up!

I hope you enjoy Access - I love it!

by peyison

A few thoughts...

First, make sure the datatype on these fields is Number or Currency. These types by default have a default of 0 - so nulls are really not an issue unless you specifically set a field to null.

Also - try to avoid dealing with nulls at all. Fields that contain nulls shouldn't be used to join tables - a null does not equal a null. So even though both fields you're joining on contain nulls, they won't be considered a "match" and you won't get the desired results.

Hope this helps.

by chrandrob

There are two commands that you need to know about: IIf and IsNull.

IsNull is a boolean call to determine if a value is null or not. If it is Null then IsNull(value) = true, otherwise it equals false.

You can either add this to a VBA module like this:

If IsNull(Field1) Then
Field3 = Field2 * 0
Else
Field3 = Field2 * Field1
End If

Or you can use IIf, called Immediate If. IIf takes three parameters. It is in the format of: IIf([test equation], [if true], [if false])

This can be used in the VBA code, or directly in queries. Here are examples of both ways to use IIf:

Field3 = Field2 * IIf(IsNull(Field1), 0, Field1)

Select IIf(IsNull(Field1), 0, Field1), Field2, (Field2 * IIf(IsNull(Field1), 0, Field1))

This discussion is closed to new replies.