Negating numbers based on criteria in another column? 2

[AJo]

Hi,

I'm hoping someone can help me.

Working in Access 97, my aim is to negate numbers in a given column, based upon criteria in another column.

i.e. Where RowType = C, negate the figure in NetCharge

I'm working with credit and debit figures pulled originally from a FoxPro application which performed this calculation, but I am more familiar with Access, therefore, I would like to solve this problem in Access.

Is it possible to change the figures in the NetCharge column, without creating another column? Ultimately, I am using this data in DI-Diver, but I require the figures to be correct in order to produce accurate reports.

Thanks in advance for your help.

 

[Cagliostro]

can you give a sample in what you want to get? John Fill

ivfmd@mail.md

 

[AJo]

Hi John,

The data looks like this (disgarding about 20 other columns):

ID NetCharge RowType
1 30 C
2 215.3 C
3 15.4 I
etc

Therefore, the NetCharge figures in ID1 and ID2 need to be -30 and -215.3 respectively. At present, these figures are shown as positive values, but the RowType value of 'C' means it is a credit (negative).

Hope that makes sense.

I could e-mail a sample of data if you wanted to see a bit more detail?

Thanks for your help.

Amanda

 

[JoeMiller]

Make a column in your query that has this equation:

ProperNetCharge: IIf([RowType]="C",[NetCharge]*-1,[NetCharge])

That should do it!

Joe Miller
joe.miller@flotech.net

 

[DRowland]

Have you tried an update query something like...

UPDATE data SET data.credit = (data.credit * -1)WHERE ((data.type) = "C" ) ;

The asterisk in the data.credit parenthesis is supposed to act as the multiplication, to make a positive number turn to negative, and visa/versa.

You might give it a try..

 

[AJo]

Hi Joe and DR,

Both of your suggestions worked great, but as I want to update the current NetCharge column, I think I'll go with the UPDATE.

Thanks again,

Amanda.

 

[JoeMiller]

Amanda:

For future reference, my suggestion would also work in an update query, just set my equation in the UpdateTo line and it will change the field. The many many ways of Access!

Joe Joe Miller
joe.miller@flotech.net