IIF statement Help

[SobJim]

Hey All,

Not sure how to finish this:
I need to it Subtract 3/7/2013[Code Date] from 04/19/2012[Min/Max] if [INC/Var]="*M*" and if [Inc/Var]="*V" I need the equation reversed.

Date Variance: IFF([Inc/Var]="*M*", ([Code Date]-[Max/Min])



Thanks!

[RuralGuy]

Are there more than these two values in [Inc/Var]?

[SobJim]

No, only "V" and "M".

[RuralGuy]

Try:
Date Variance: IFF([Inc/Var]="*M*", [Code Date]-[Max/Min], [Max/Min]-[Code Date])

[SobJim]

It is applying the False result to the whole column.
Thanks

[RuralGuy]

Meaning that the field [Inc/Var] is never equal to the string "*M*"! Is that true?

[SobJim]

Yeah guess so.

In the field [Inc/Var] all there is, are spaces and then either an "M" or "V".

For the Criteria on [Inc/Var] I have it set to Like "*M*" Or "*V". To omit some other lines I don't need.

[SobJim]

The column has "M"s in them. I have a feeling its the spaces in front of the M or V causing the issue. The Table is imported from a TXT file, is there a way I can have it remove the spaces automatically?

[RuralGuy]

Try:
Date Variance: IFF(InStr([Inc/Var],"M"), [Code Date]-[Max/Min], [Max/Min]-[Code Date])

[SobJim]

Thanks!!!! That did it!!

[RuralGuy]

You're welcome.