Solved

How do I split a column?

Posted on 2009-05-20
10
202 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

828 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