Microsoft format function in query and dealing with nulls

[BXB]

I have a number in a table that starts off as currency. There are nulls in the data so I'm trying to convert the nulls to zeros and then wind up back in currency, or in fact in any numeric format. I've tried both of these.


Add: Format(nz([Adds],0),"Standard")
Add: Format(nz([Adds],0),"$#,##0.00")


With this I wind up with what looks like numbers but when I export to Excel or try to subtotal at the bottom of the query I don't have numbers that will add up?

I'm kind of new to this if you can't tell. HELP and THANKS!

[puppydogbuddy]

I have a number in a table that starts off as currency. There are nulls in the data so I'm trying to convert the nulls to zeros and then wind up back in currency, or in fact in any numeric format. I've tried both of these.


Add: Format(nz([Adds],0),"Standard")
Add: Format(nz([Adds],0),"$#,##0.00")


With this I wind up with what looks like numbers but when I export to Excel or try to subtotal at the bottom of the query I don't have numbers that will add up?

I'm kind of new to this if you can't tell. HELP and THANKS!

If you have a text data type, try converting to numeric via VAL function and see if that helps. Not sure what order the functions have to take place.

Format(nz(Val([Adds]),0),"Currency")
or
nz(Format(Val([Adds]),"Currency"),0)

[BXB]

Tried it and it still didn't work, same issue, neither Access or Excel is recognizing this data as a number and I can't get it to sum!

Any other ideas?

[puppydogbuddy]

Tried it and it still didn't work, same issue, neither Access or Excel is recognizing this data as a number and I can't get it to sum!

Any other ideas?

i think I may have had functions working in the wrong order, try it this way. It makes more sense given that the Val function works on just numerical characters.:

Format(Val(nz([Adds], 0)),"Currency")

or in case Val is not working, try this (CDbl converts to a double precision number:)

Format(CDbl(nz([Adds], 0)),"Currency")

[BXB]

It didn't work, but you made me realize importance of what comes first so I tried

Val(nz(format([Adds],0),"Currency"))

And it worked....this forum is great!

[puppydogbuddy]

It didn't work, but you made me realize importance of what comes first so I tried

Val(nz(format([Adds],0),"Currency"))

And it worked....this forum is great!

Glad we could help you get it resolved. Thanks for posting back with the final solution. It should help a lot of Access users and developers. Now that I think about this, it makes sense that val would be the last function applied since it returns only numeric values.