Null Values in Query

[sunnygirl]

I am running a query to provide a list of vendors and a sum of the total purchases in dollars. I did change the join properties to include those vendors were zero dollars have been purchased.

How do I show $0.00 for those vendors and not just a blank?

Thanks for the assist.

SunnyGirl

 

[o1110010]

... How do I show $0.00 for those vendors and not just a blank?

And you've set the format for the appropriate data to Currency? But all the other data displays in currency format?

 

[sunnygirl]

Yes, I data type is set to currency for that field.

And you've set the format for the appropriate data to Currency? But all the other data displays in currency format?

 

[o1110010]

Ohh! My mistake. I thought the values were coming up as ZERO and not NULL. Sorry.

 

[o1110010]

Expr1: CCur(Nz([curValue],0))

 

[sunnygirl]

When I go to print the query, I would like the field to show $0.00 instead of being a blank.

I am testing with the expression "IIF(IsNull[SumOfPurchaseAmount], "$0.00").
I keep getting an invalid syntax error.

Ohh! My mistake. I thought the values were coming up as ZERO and not NULL. Sorry.

 

[o1110010]

When I go to print the query, I would like the field to show $0.00 instead of being a blank.

I am testing with the expression "IIF(IsNull[SumOfPurchaseAmount], "$0.00").
I keep getting an invalid syntax error.

Just in case you, like myself, wait to see a post as new before going back into a thread. I am adding another post.. but basically check out the last post. I wrote it while you were writing yours so.... the thread might appear "read" to you.

 

[sunnygirl]

Ok. Tried that. When executed it asks for a parameter value. If I leave it blank and hit ok, it returns the result I am looking for. But, I have to turn this project in and would get points taken off.

Any other hints??

BTW... thanks for helping me.

Expr1: CCur(Nz([curValue],0))

 

[o1110010]

You're welcome. :-D

Expr1: CCur(Nz([curValue],0))

Just to make sure you put it in the right spot and made the correct changes (since I forgot to tell you )

In your query, it would go in a Field (top row) value. Then you'd change [curValue] to whatever expression or source you were originally using. So.. I think for YOU it would actually be:

Expr1: CCur(Nz([SumOfPurchaseAmount],0))

 

[sunnygirl]

I did change the expression to SumOfPurchaseAmount and put it in the field field.

When executed, it still is looking for a parameter value.

You're welcome. :-D

Expr1: CCur(Nz([curValue],0))

Just to make sure you put it in the right spot and made the correct changes (since I forgot to tell you )

In your query, it would go in a Field (top row) value. Then you'd change [curValue] to whatever expression or source you were originally using. So.. I think for YOU it would actually be:

Expr1: CCur(Nz([SumOfPurchaseAmount],0))

 

[o1110010]

I did change the expression to SumOfPurchaseAmount and put it in the field field.

When executed, it still is looking for a parameter value.

Is [SumOfPurchaseAmount] a field in a table? Where does this get calculated?

 

[sunnygirl]

It is calculated in the query. The field is called PurchaseAmount and I am running a sum. The query generates "SumOfPurchaseAmount".

Now that I think of it...I might need to run a query from the query??

Is [SumOfPurchaseAmount] a field in a table? Where does this get calculated?

 

[o1110010]

It is calculated in the query. The field is called PurchaseAmount and I am running a sum. The query generates "SumOfPurchaseAmount".

Now that I think of it...I might need to run a query from the query??

Yup, Maybe! But try putting Expr1: CCur(Nz([SumOfPurchaseAmount],0)) further to the right of [SumOfPurchaseAmount] and see if that works.

 

[sunnygirl]

I tried moving the expression to the right and it still wanted a parameter value. So I got the results I wanted by running another query off the original one.

Thanks for the help!!

(Now moving on to the next problem... )

Yup, Maybe! But try putting Expr1: CCur(Nz([SumOfPurchaseAmount],0)) further to the right of [SumOfPurchaseAmount] and see if that works.

 

[o1110010]

I tried moving the expression to the right and it still wanted a parameter value. So I got the results I wanted by running another query off the original one.

Thanks for the help!!

(Now moving on to the next problem... )

Glad we got it worked out. Now I can concentrate on my work.

Btw, You're welcome. Any time.. except when I have work to do