If Statement for a Query Using SUM (1 Viewer)

[jcruzAME]

Is there a way to write an if statement into a query with a SUM column that says if the query is null, return 0?

The query that I'm is trying to total an amount of a certain type of asset, Insurance. But, it is possible for certain sets of clients to have none in that category.

 

[Alansidman]

It would be easier to understand what you are trying to do if you gave us an example with data. Show us what you have, Show us the expected result and lets see if we can solve it for you. What you have already provided is to vague to determine a valid response.

 

[jcruzAME]

So I have one table that has all Client assets. One category is Investments.

One tab on the navigation form is Investments, and it will pull all the information on Investments under a certain advisor.

The query that runs basically says get all the investments for the currently selected advisor.

Some advisors though, might have only one or two clients, those of which have 0 investment assets.

Currently, if I run the query to total all of the client investments for an advisor, sometimes this will come back empty. It's causing a problem when I try to total up all of the client investments for those advisors.

So I need an if statement that says if it runs the query and if the totals column is empty, to make it equal to 0.

Currently the only way I can think to fix this is if I have an if statement on the SUM in that column.

 

[raskew]

Hi -

Check-out the nz function in the help file. It should give you what you need.

Best Wishes - Bob