• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • 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
  • 2
2 Solutions
Rey Obrero (Capricorn1)Commented:

=N1 + N2


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.


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.
=Avg(yourfield) --- The Avg function counts the non-null values to get the divisor so

2, 0, 4 returns 2
2, null, 4 returns 3
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
davetoughAuthor Commented:
thank you for help and explanation
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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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