Converting a string to number format

[Guest]

I use the code listed below in a query. I want the output to be Double
format when the If statement is FALSE and string when it is TRUE. When I run
the query now all of the cells are string, If I replace the "-" with a zero
or NULL, the output will be Double format. How can I output both string and
Double?


IIf([Digging_GapOutput]![L-BrandCases]=0,"-",([Digging_GapOutput]![BrandCases]-[Digging_GapOutput]![L-BrandCases])/[Digging_GapOutput]![L-BrandCases]*100)

 

[Tom Wickerath]

Hi Matt,

Short answer is to write a custom function using VBA code, which is called by your query.

I haven't tested this, but you might try modifying your IIF statement by adding the type
conversion function "CDbl" (Convert to Double):

IIf([Digging_GapOutput]![L-BrandCases]=0,"-",CDbl(([Digging_GapOutput]![BrandCases]-[Digging_GapO
utput]![L-BrandCases])/[Digging_GapOutput]![L-BrandCases]*100))

Of course, you'll need to guard against [Digging_GapOutput]![L-BrandCases] ever being zero, null
or a zero length string, since you are using this as the denominator in your calculation.

Tom
____________________________________


I use the code listed below in a query. I want the output to be Double
format when the If statement is FALSE and string when it is TRUE. When I run
the query now all of the cells are string, If I replace the "-" with a zero
or NULL, the output will be Double format. How can I output both string and
Double?


IIf([Digging_GapOutput]![L-BrandCases]=0,"-",([Digging_GapOutput]![BrandCases]-[Digging_GapOutput
]![L-BrandCases])/[Digging_GapOutput]![L-BrandCases]*100)