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

[Anonymous]

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!

[tarunsingla · Accepted solution]

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!

[tarunsingla]

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!

[Anonymous]

Thank you Tarunsingla, however, neither of those options worked 😞

[tarunsingla · Accepted solution]

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!

[Anonymous · Accepted solution]

Thank you again, have a nice day!