how to add if a field is negative, but add if its positive?

[icemonster]

how do i accomplish this?

let's say, if field a is negative and field b is positive, it will add, but if field is positive and field b is positive, it will subtract? how do i write this in a report or query? thanks

 

[dkinley]

So you want ...

-x + +y
+x - +y

In a query column try something like ...

VariantName: IIF([fldFieldx] > 0, [fldFieldx] - [fldFieldy], [fldFieldx] + [fldFieldy])

I should add that what happens if one of the fields are blank or empty?

You could use the Nz function on each of the fields to insert a 0 or perhaps utilize the IsError in some fashion.

-dK

 

[icemonster]

how about in a report?

 

[dkinley]

If your report is driven from the query, then whatever you used as the VariantName - set that to the control source of the control you want it to display in.

If not, then you can use the IIF function in the control source, too.

-dK

 

[icemonster]

thanks. it worked. one last thing, i ahve been scouring this forum for a problem same as mine, i have a report that has a subreport, when i convert or print it, somehow the subreport doesnt show? any ideas?

 

[dkinley]

You are welcome!

I would guess that the subreport doesn't have any data in it. Only things I can think of is checking the Parent/Child links on the subreport properties if those are required, or setting it all up in a big query to check that I am getting the data I actually want and possibly troubleshooting it there.

-dK

 

[icemonster]

hey actually, kinda something wrong with the code

i wrote it like this iif([text1] >0, [text2] - [text1], [text1] + [text2])

it only seems to do the subtraction part, does not do the adding.

 

[dkinley]

Are those numeric fields you are working with? That is, declared at the table level as numbers? Or, are these some unbound controls on a form?

-dK

 

[icemonster]

yes they are, actualyl they calculated epxressions too. thats why i had to do the if negative or positive because the calculated expression could yield either two.

 

[dkinley]

Hmmm. So it tested one way fine, but not the other.

Might need to use a CInt(...) around those to ensure that they will be manipulated as integers.

I will perform a check, too.

-dK

 

[icemonster]

still not doing anything, perhaps its because its a calculated expression to begin with? actually, its odd, because it only does the subtraction not the addition.

 

[boblarson]

still not doing anything, perhaps its because its a calculated expression to begin with? actually, its odd, because it only does the subtraction not the addition.

Can you give an example using real numbers as to what you are seeing and what you are expecting to see?

 

[dkinley]

I am not sure why it is not working. I've attached my screenshot where it works.

-dK

 

[icemonster]

okay, here's a sample

fieldA fieldB

4 -3 = 1
-4 -4 = 0
5 2 =7

 

[dkinley]

Hmmm ... that 5|2 definitely doesn't compute.

Just for grins ... modify your IIF to this ...

IIf(CInt([text1]) > 0, CInt([text2]) - CInt([text1]), CInt([text1]) + CInt([text2]))

-dK

 

[icemonster]

ah am getting invalid arguments, please do know that am doing this in the report not in the queries.

 

[dkinley]

Okay ... to surmise so I can stall for time and think

The IIF is used in a control source of an unbound control in report?

The controls referenced [textn]are calculated controls (based on other controls) or are they bound controls (coming from an underlying table/query)?

-dK

 

[boblarson]

Try this instead:

= IIf([FieldB]<0 And [FieldA]<0,[FieldA]+Abs([FieldB]),IIf([FieldB]<0 And [FieldA]>=0,[FieldB]+[FieldA],[FieldA]-[FieldB]))

 

[icemonster]

yes. the controls are calculated expression from the report too not from the source query.

@bob, ill try this one too.

 

[dkinley]

I've attached a report screen and the original proposed solution appears to work.

The first two columns (A & B) are bound and fed from a table. Column X & Y are unbound and calculated (e.g., [A] + 2). Last, column Z is unbound using the IIF in discussion.

Perhaps you can post the db?

-dK