Separating negative values.

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

I have a bank account where I can collect the data as a CSV file.

For some peculiar reason the debits and credits are delivered in one
column. The debits are shown as negative values.

How do I write code to place negative values in a filed called
"Debits" and the positive values in a filed called "Credits"?

Thanks,

Robin Chapple
 
I have a bank account where I can collect the data as a CSV file.

For some peculiar reason the debits and credits are delivered in one
column. The debits are shown as negative values.

How do I write code to place negative values in a filed called
"Debits" and the positive values in a filed called "Credits"?

Thanks,

Robin Chapple
Click to expand...

Use an Update query:
UPDATE YourTable SET YourTable.Credits =
IIf([FieldName]>0,[FieldName]), YourTable.Debits =
IIf([FieldName]<0,[FieldName])
WHERE (((YourTable.FieldName) Is Not Null));

The above will place the negative value in the Debit column with the
minus sign. If you wish to place the value in the field without the
minus sign, use
YourTable.Debits = IIf([FieldName]<0,ABS([FieldName]))
 
Robin,

Debits and credits are in one column is the correct way to store this data!
That's correct accounting. They are being recorded as Transactions and you
should leave them this way!! This makes it easy to get the balance at any point.
In your bank account you get the Credits by using a query that includes the
Transactions field and setting the criteria to >=0. You get the Debits from a
similar query but the criteria for Transactions is <=0.
 
Back
Top