split positive and negative numbers in 2 columns

[megatronixs]

Hi all,

I have a table that has a field where positive and negative numbers are stored.
I was wondering if it is possible to split them in the query so the positive numbers appears in one column and the negative in another column..

this would the original:
Reserve
12.00
-45.78
-34.45
25.34

this could be the split:
Reserve Debit - Reserve Credit
---------------12.00
-45.78
-34.45
---------------25.34

(I had to put those minus signs to keep the format)

Greetings.

 

[pbaldy]

Along the lines of

Debit: IIf(Reserve > 0, Reserve, 0)

 

[megatronixs]

Hi,
I get error: the expression you entered has an invalid . (dot) or ! operator or invalid parentheses
you may have entered an invalid identifier or typed parentheses following the Null constant.

I don't nothing strange, but the error keeps showing up.

Greetings.

 

[pbaldy]

What is the SQL of your query now?

 

[megatronixs]

Hi,

I made a simply query with some dummy data:

SELECT tbl_data.date_field, tbl_data.Reserve, tbl_data.account
FROM tbl_data;

It is as simple as it gets, just to give it a try.

Greetings.

 

[megatronixs]

Silly me, I did put it in the criteria field :-(
no wonder

Greetings.

 

[pbaldy]

Glad you got it sorted out.

 

[megatronixs]

Hi,
Thanks a lot.

I was wondering how to turn it around to make it Credit for the second column.
this will not work:

Credit: IIf([Reserve],0[Reserve] >0,)

or I'm just missing something silly?

I actually noticed that with the negative numbers, they are just 0. can that be fixed that it shows -12.45 ?
Greetings.

 

[megatronixs]

Ok, solved

Debit: IIf([Reserve]<0,[Reserve],"")

and

Credit: IIf([Reserve]>0,[Reserve],"")

Sometimes the little details can be silly

Greetings.

 

[pbaldy]

Happy to help. You may want to account for zero in one of those, with either >= or <=.