Improve company productivity with a Business Account.Sign Up

x
?
Solved

How do I split a column?

Posted on 2009-05-20
10
Medium Priority
?
209 Views
Last Modified: 2012-05-07
Now I have one column that has positive and negative values, Now I want to split this column the positive and negative shoaled be separated

how do I do it?

Thanks for any help
0
Comment
Question by:AYid
  • 5
  • 5
10 Comments
 
LVL 15

Expert Comment

by:MNelson831
ID: 24435327
Select
     Case
          When MyFieldValue < 0 then MyFieldValue
          Else 0
     End as MyNegNumbers,
     Case
          When MyFieldValue > 0 then MyFieldValue
          Else 0
     End as MyPosNumbers
From
     MyTableName
Where
     MyCriteria = True



     
0
 
LVL 1

Author Comment

by:AYid
ID: 24435388
I'm getting an error

syntax error
0
 
LVL 1

Author Comment

by:AYid
ID: 24435417

syntax error (missing operator) in query expression

Open in new window

0
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'.

 
LVL 15

Expert Comment

by:MNelson831
ID: 24435435
Are you running this in Access or SQL?
0
 
LVL 1

Author Comment

by:AYid
ID: 24435460
Accses
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 24435465
SELECT IIf([MyDataField]>0,[MyDataField],0) AS MyPosNums, IIf([MyDataField]<0,[MyDataField],0) AS MyNegNums
FROM MyTableName;
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 24435476
Look in this file for example
db9.mdb
0
 
LVL 1

Author Comment

by:AYid
ID: 24435701
Thanks it works great!!!

one more thing.. what is the syntax to format it as currency?
0
 
LVL 15

Accepted Solution

by:
MNelson831 earned 500 total points
ID: 24435996
SELECT Format(IIf([MyDataField]>0,[MyDataField],0),"Currency") AS MyPosNums, Format(IIf([MyDataField]<0,[MyDataField],0),"Currency") AS MyNegNums
FROM MyTableName;

0
 
LVL 1

Author Closing Comment

by:AYid
ID: 31583663
Thanks
0

Featured Post

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'.

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.

Join & Write a Comment

Implementing simple internal controls in the Microsoft Access application.
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question