Convert Some field Entries to negative Numbers

  • Thread starter Thread starter Thorson
  • Start date Start date
T

Thorson

I have query set up that counts the number of entries in each category
("died" "sold" "euthanized" "born") in a specified field from a table. I
would like to change some of the category 'counts' to negative numbers while
leaving others positive. This way I can add up the total change in animal
numbers for a report.

An IIF statement did work, however the statement was too long for the query
field. The shortened statment is as follows:
NumberChanged:
IIf([DispMethod]="Died",([CountOfDispMethod]*-1),IIf([DispMethod]="Sold",([CountOfDispMethod]*-1)))

This equation works fine, but I still have 2 more categories to make
negative, which makes the equation to many characters to fit into the field.
Any suggestions?

Thanks
 
NumberChanged : iif( DisMethod IN("died", "sold", "xxx", "yyy"), -1, 1) *
ABS(countOfDispMethod)


Vanderghast, Access MVP
 
Thank you! Works Great!

Michel Walsh said:
NumberChanged : iif( DisMethod IN("died", "sold", "xxx", "yyy"), -1, 1) *
ABS(countOfDispMethod)


Vanderghast, Access MVP


Thorson said:
I have query set up that counts the number of entries in each category
("died" "sold" "euthanized" "born") in a specified field from a table. I
would like to change some of the category 'counts' to negative numbers
while
leaving others positive. This way I can add up the total change in animal
numbers for a report.

An IIF statement did work, however the statement was too long for the
query
field. The shortened statment is as follows:
NumberChanged:
IIf([DispMethod]="Died",([CountOfDispMethod]*-1),IIf([DispMethod]="Sold",([CountOfDispMethod]*-1)))

This equation works fine, but I still have 2 more categories to make
negative, which makes the equation to many characters to fit into the
field.
Any suggestions?

Thanks
Click to expand...
Click to expand...
 
Back
Top