• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

if column in table has no values - search does not work

am attaching sample database- 3 fields in table
id field/ N1 /N2
if there is not a number in the N1 or N2 fields ( or a zero) search does not work- so when import numbers in - there are no zeros
Is there anyway to make this work without adding zero's

open database - open search form- enter 1017 - number should display - but enter 133 and nothing
thank you
Database-Search.accdb
0
davetough
Asked:
davetough
  • 2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
change

=N1 + N2

to

=Nz([N1])+Nz([N2])
0
 
PatHartmanCommented:
I like to be explicit with functions like this.  Nz() operates differently with different data types so to avoid unexpected results, always specify what you want the expression to return.

=Nz([N1],0)+Nz([N2],0)

You will need to use the Nz() function whenever you are doing calculations that include a column that might be null.  However, domain functions will ignore nulls and so don't have a problem.
=Sum(yourfield)
=Avg(yourfield) --- The Avg function counts the non-null values to get the divisor so

2, 0, 4 returns 2
but
2, null, 4 returns 3
0
 
Rey Obrero (Capricorn1)Commented:
in using the Nz(variant, valueifnull) function, the "valueifnull" is optional unless you use the nz() function in a query.

in this case, specifying the optional value is redundant.
if the [variant] is null, nz will return a value of zero or zero length string, depending on the data type of [variant], Number or Text

?null + 2  will give you null
?nz(null) + 2  will give you 2

see this link for more info
http://office.microsoft.com/en-us/access-help/nz-function-HA001228890.aspx
0
 
davetoughAuthor Commented:
thank you for help and explanation
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now