set null values to zero in an access query using linked tables

T

thedean_kevin

ok i set up a database that uses info from an oracle server thru ms sql. the
output data is in the form of excel sheets and are linked to an access
database (linked tables). the problem i designed a query using these linked
tables to give an account of customers and their balances for five different
companies. There is a table with all the customer names and the rest has a
joint property to display the balances with any matching names in that table.
So we have a datasheet from the query that has six fields (the customer names
and the five matching balances) however not all the balances would match with
all the names, hence i have alot of null or blank rows in the query/datasheet
preview. the problem is i've also created a form that shows all this
information along with a formula that calculates the total for all five
balances. However because some of the balances might be a null value that
correspond to a specific customer name, the formula doesn't work. it only
works if all five balances have values. so i need to know how i can change
those null values to zeros so as to get the total balances working (again i'm
working with linked tables) so firstly is their any restriction in achieving
this and if ther is how can i get pass it. Secondly i'm desperate and need
any help i can get, i have deadlines to meet with this database so i need
help asap.

to give you an idea of what the query looks like below is a copy of the sql
script for the query, it's simple but the null to zero thing is tricky.

SELECT [CUSTOMER NAMES].NAME, [AINLIM AR DATABASE].BALANCE1, [CSS AR
BALANCE].BALANCE2, [DOCOL AR BALANCE].BALANCE3, [GEDDES GRANT AR
BALANCE].BALANCE4, [NMSL AR BALANCE].BALANCE5
FROM (((([CUSTOMER NAMES] LEFT JOIN [AINLIM AR DATABASE] ON [CUSTOMER
NAMES].NAME = [AINLIM AR DATABASE].NAME) LEFT JOIN [CSS AR BALANCE] ON
[CUSTOMER NAMES].NAME = [CSS AR BALANCE].NAME) LEFT JOIN [DOCOL AR BALANCE]
ON [CUSTOMER NAMES].NAME = [DOCOL AR BALANCE].NAME) LEFT JOIN [GEDDES GRANT
AR BALANCE] ON [CUSTOMER NAMES].NAME = [GEDDES GRANT AR BALANCE].NAME) LEFT
JOIN [NMSL AR BALANCE] ON [CUSTOMER NAMES].NAME = [NMSL AR BALANCE].NAME

thank you for any help i can get

kevin
 
O

OfficeDev18 via AccessMonster.com

Try the Nz() function. Like this: [AINLIM AR DATABASE].BALANCE1, [CSS AR
BALANCE].BALANCE2, becomes Nz([AINLIM AR DATABASE].BALANCE1,0) As Bal1, Nz(
[CSS AR BALANCE].BALANCE2,0) As Bal2, ...... etc. Nz changes null values to 0
(or any valid number) for numbers, or " " (or any valid string) for strings.
Look it up in the Help.


thedean_kevin said:
ok i set up a database that uses info from an oracle server thru ms sql. the
output data is in the form of excel sheets and are linked to an access
database (linked tables). the problem i designed a query using these linked
tables to give an account of customers and their balances for five different
companies. There is a table with all the customer names and the rest has a
joint property to display the balances with any matching names in that table.
So we have a datasheet from the query that has six fields (the customer names
and the five matching balances) however not all the balances would match with
all the names, hence i have alot of null or blank rows in the query/datasheet
preview. the problem is i've also created a form that shows all this
information along with a formula that calculates the total for all five
balances. However because some of the balances might be a null value that
correspond to a specific customer name, the formula doesn't work. it only
works if all five balances have values. so i need to know how i can change
those null values to zeros so as to get the total balances working (again i'm
working with linked tables) so firstly is their any restriction in achieving
this and if ther is how can i get pass it. Secondly i'm desperate and need
any help i can get, i have deadlines to meet with this database so i need
help asap.

to give you an idea of what the query looks like below is a copy of the sql
script for the query, it's simple but the null to zero thing is tricky.

SELECT [CUSTOMER NAMES].NAME, [AINLIM AR DATABASE].BALANCE1, [CSS AR
BALANCE].BALANCE2, [DOCOL AR BALANCE].BALANCE3, [GEDDES GRANT AR
BALANCE].BALANCE4, [NMSL AR BALANCE].BALANCE5
FROM (((([CUSTOMER NAMES] LEFT JOIN [AINLIM AR DATABASE] ON [CUSTOMER
NAMES].NAME = [AINLIM AR DATABASE].NAME) LEFT JOIN [CSS AR BALANCE] ON
[CUSTOMER NAMES].NAME = [CSS AR BALANCE].NAME) LEFT JOIN [DOCOL AR BALANCE]
ON [CUSTOMER NAMES].NAME = [DOCOL AR BALANCE].NAME) LEFT JOIN [GEDDES GRANT
AR BALANCE] ON [CUSTOMER NAMES].NAME = [GEDDES GRANT AR BALANCE].NAME) LEFT
JOIN [NMSL AR BALANCE] ON [CUSTOMER NAMES].NAME = [NMSL AR BALANCE].NAME

thank you for any help i can get

kevin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top