CODEWITHSUNDEEP
sqlms-access
matjan2
matjan2

Reputation: 13

How to change text to number in query

I have this query

SELECT SUM (pw. [Plan]) AS [Plan],
       SUM (pw.[execution]) AS [Execution],
       ([Execution] - [Plan]) AS [Deviation],
       (IIf ([Plan]> 0, [Deviation] / [Plan], 'No plan')) AS [Deviation%]...

The problem is when I'm using IIf the results of query in Excel are seen as text, without it it's ok. How to change it to be visible as number? Or maybe can I make condition that plan must be higher than 0 in another way?

Upvotes: 0

Views: 91

Answers (1)

forpas
forpas

Reputation: 164099

The return value of this expression:

IIf ([Plan]> 0, [Deviation] / [Plan], 'No plan')

is recognized as text since 'No plan' is text.
If both the true and false parts of IIf were numeric, like:

IIf ([Plan]> 0, [Deviation] / [Plan], 0)

then Excel would see the result as a number.

Upvotes: 1

Related Questions