switch statement causing currency to be converted to string

help guys! using the switch statement converts my numbers AND currencies in to string!

what should i do what should i do what should i dooooooo wooo woooo???!!!!

i can encapsulate the result of switch in a ccur function and that solves the problem but..... i'm a purist and i need to know why switch is treating me so bad!!! help guys!!

how can we use switch without it converting numbers and currencies into string?! thanks guys!!!! = )))

AND GUYS!! i just found out!!

switch(currencyfield>0,ccur(currencyfield)) doesn't work!!!!!
only ccur(switch(currencyfield>0,currencyfield)) works!!!!

so that means guys, my statement of

Open in new window

is almost doomed to get Error#!!!! heeeelelppppp!! batman has left gotham!! BANE IS COMING!!!!!!
Who is Participating?
Jim P.Connect With a Mentor Commented:
From MS
Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.

So what is happening is that it may be reading a numeric data type from the table, it effectively returning a string. That's because you could have code that looks like:
switch(currencyfield>0,ccur(currencyfield), isnull(currencyfield), "N/A", currencyfield>100,"Over Charge" ) 

Open in new window

So the function does it this way.
Dale FyeCommented:
Where are you using this Switch() function call?  Query or va?
Dale FyeConnect With a Mentor Commented:
In this case, since you really only want two different return values, you might consider a simple IIF( ) function call:

IIF ([ApprovalAmount] >0 [ApprovalAmount], Null)
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

"how can we use switch without it converting numbers and currencies into string?"

The results in the query are numeric, they are right justified. String is left justified.

So I am unable to see the issue.
developingprogrammerAuthor Commented:
hnasr, thanks for looking into this! all the swtich function calculated fields are left justified as shown in the attached database.
developingprogrammerAuthor Commented:
thanks Jim! that's exactly what i was looking for! i should have searched it in the help first!

thanks fyed! i did that initially but switched to switch statement for learning / test purpose for awhile. i went back to iif again already! thanks!! = ))
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.