Queries

[iamapollo]

Hi

My question relates to designing a query. I have made a field in the query that adds the value in three other fields, in the form 'Totals: [field1] + [field2] + [field3].

This works fine when all three fields have values, but if one or 2 of the fields are blank it comes out with an answer of blank even if one or two of the fields do have values in them. Any ideas how I can fix this?

Thanks much

Michael

 

[lth]

The simple answer (I don't know if there's a more elegant one) would be:

Totals: Iif(IsNull[field1],0,[field1] + Iif(IsNull[field2],0,[field2] + Iif(IsNull[field2],0,[field2];

This *should* work but it's off the top of my head. Look in VBA's help under 'iif' for its correct syntax if not.

Cheers

K

 

[cyberbiker]

I use the NZ function. Don't really know which way is "best", just thought I would offer an alternative. I find the nz function easier for me, but really do not know which way executes faster

from Access 97 help:
Syntax

Nz(variant[, valueifnull])

The Nz function has the following arguments.

Argument Description
variant A variable of data type Variant.
valueifnull Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string. If you use the Nz function in an expression in a query without using the ValueIfNull argument, the results will be empty in the fields that contain null values

Thus:
your query would be like this sum( nz([field1],0) + nz(field2], 0) + nz([field3],0)) as Totals (or whatever) Terry (cyberbiker)