Solved

How do I split a column?

Posted on 2009-05-20
10
198 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 125 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now