cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JeremyGieser Frequent Visitor
Frequent Visitor

Return a value of 0 in a query that includes text.

Hi all,

 

Here is my code:

 

TotalValetSite = Format(FactLots[Total Valet],"#,###") & " total valets this week."
 
 
I sums the number of valet parked cars for the week. As long as we park 1 or more cars it works great!  but when we do not park any cars it returns the following: 
 
"  total valets this week."
 
How do I get this code to return a zero as follows:
"0 total valets this week."
 
Thank you much!
2 ACCEPTED SOLUTIONS

Accepted Solutions
tarunsingla Member
Member

Re: Return a value of 0 in a query that includes text.

My bad, didn't realize the formatting used in your formula.

Try those suggestions without the Format function to replace the blanks with 0.

Or change your format from ("#,###" to "0,0") and see if that resolves.

 

More on formatting here:

https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings

 

Regards,

Tarun
Did I answer your question? Mark my post as a solution!

JeremyGieser Frequent Visitor
Frequent Visitor

Re: Return a value of 0 in a query that includes text.

Thank you again, have a nice day!

4 REPLIES 4
tarunsingla Member
Member

Re: Return a value of 0 in a query that includes text.

Try this: Add 0 to your [Total Valet] measure.

TotalValetSite = Format(FactLots[Total Valet] + 0,"#,###") & " total valets this week."

 

If that does not work, try the following:

TotalValetSite = Format(IF(FactLots[Total Valet] = BLANK(), 0 , FactLots[Total Valet]),"#,###") & " total valets this week."

 

Regards,

Tarun
Did I answer your question? Mark my post as a solution!

 

JeremyGieser Frequent Visitor
Frequent Visitor

Re: Return a value of 0 in a query that includes text.

Thank you Tarunsingla, however, neither of those options worked Smiley Sad

tarunsingla Member
Member

Re: Return a value of 0 in a query that includes text.

My bad, didn't realize the formatting used in your formula.

Try those suggestions without the Format function to replace the blanks with 0.

Or change your format from ("#,###" to "0,0") and see if that resolves.

 

More on formatting here:

https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings

 

Regards,

Tarun
Did I answer your question? Mark my post as a solution!

JeremyGieser Frequent Visitor
Frequent Visitor

Re: Return a value of 0 in a query that includes text.

Thank you again, have a nice day!