|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 29th, 2004, 09:21 PM
|
|||
|
|||
Adding fields in a query
I have several fields with values in them and some of the fields are null.
In Access 2000, I am adding these fields together, but the only totals I receive are when both fields have a value. Is this something to do with Null values and is there a workaround? Example: Field 1 Field 2 Field 3 Total 5 7 7 19 3 6 Thanks in advance Dave 
|
|
#2
November 29th, 2004, 09:42 PM
|
|||
|
|||
|
How are you adding them? What's your formula look like?
"Dave" wrote in message ... I have several fields with values in them and some of the fields are null. In Access 2000, I am adding these fields together, but the only totals I receive are when both fields have a value. Is this something to do with Null values and is there a workaround? Example: Field 1 Field 2 Field 3 Total 5 7 7 19 3 6 Thanks in advance Dave
|
|
#3
November 29th, 2004, 09:49 PM
|
|||
|
|||
|
You are correct. Rather than adding
Field1+Field2+Field3, use Nz(Field1,0)+Nz(Field2,0)+Nz(Field3,0). The Nz() function will substitute 0's for nulls. HTH, Ted Allen "Dave" wrote: I have several fields with values in them and some of the fields are null. In Access 2000, I am adding these fields together, but the only totals I receive are when both fields have a value. Is this something to do with Null values and is there a workaround? Example: Field 1 Field 2 Field 3 Total 5 7 7 19 3 6 Thanks in advance Dave
|
|
#4
November 29th, 2004, 11:02 PM
|
|||
|
|||
|
That did it!!
Thanks. Dave "Ted Allen" wrote in message ... You are correct. Rather than adding Field1+Field2+Field3, use Nz(Field1,0)+Nz(Field2,0)+Nz(Field3,0). The Nz() function will substitute 0's for nulls. HTH, Ted Allen "Dave" wrote: I have several fields with values in them and some of the fields are null. In Access 2000, I am adding these fields together, but the only totals I receive are when both fields have a value. Is this something to do with Null values and is there a workaround? Example: Field 1 Field 2 Field 3 Total 5 7 7 19 3 6 Thanks in advance Dave
|
|
#5
November 30th, 2004, 12:45 AM
|
|||
|
|||
|
My pleasure, glad it helped.
"Dave" wrote: That did it!! Thanks. Dave "Ted Allen" wrote in message ... You are correct. Rather than adding Field1+Field2+Field3, use Nz(Field1,0)+Nz(Field2,0)+Nz(Field3,0). The Nz() function will substitute 0's for nulls. HTH, Ted Allen "Dave" wrote: I have several fields with values in them and some of the fields are null. In Access 2000, I am adding these fields together, but the only totals I receive are when both fields have a value. Is this something to do with Null values and is there a workaround? Example: Field 1 Field 2 Field 3 Total 5 7 7 19 3 6 Thanks in advance Dave
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Unmatched Query Mess | Natalia | Running & Setting Up Queries | 8 | October 28th, 2004 02:36 PM |
| query fields | Elsie | General Discussion | 4 | August 24th, 2004 06:05 AM |
| Query to join records form 2 databases | bdehning | General Discussion | 5 | August 9th, 2004 03:09 PM |
| Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |
| how to combine fields on a form in a query as criteria? | Henro | New Users | 3 | June 8th, 2004 03:50 PM |
Linear Mode
