By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,645 Members | 1,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,645 IT Pros & Developers. It's quick & easy.

Microsoft format function in query and dealing with nulls

P: 3
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!
Oct 3 '07 #1
Share this Question
Share on Google+
5 Replies


puppydogbuddy
Expert 100+
P: 1,923
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)
Oct 3 '07 #2

P: 3
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?
Oct 4 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
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")
Oct 4 '07 #4

P: 3
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!
Oct 4 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
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.
Oct 4 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.