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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim P.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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
Where are you using this Switch() function call?  Query or va?
Dale FyeCommented:
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)
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

"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!! = ))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.