?
Solved

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

Posted on 2014-03-15
4
Medium Priority
?
306 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
  • 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 40

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

850 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