Convert Some field Entries to negative Numbers

[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

 

[Michel Walsh]

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


Vanderghast, Access MVP

 

[Thorson]

Thank you! Works Great!

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


Vanderghast, Access MVP

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