[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-03-15
4
Medium Priority
?
304 Views
Last Modified: 2014-03-15
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
Comment
Question by:davetough
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1600 total points
ID: 39931431
change

=N1 + N2

to

=Nz([N1])+Nz([N2])
0
 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 400 total points
ID: 39931579
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39931660
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
 

Author Closing Comment

by:davetough
ID: 39932032
thank you for help and explanation
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question