Validation from one field to another

[bknight]

I want to revisit this. I created a brand-new table with two fields
Type--Text
Quantity--Number
inserted[Type] Like "Sell*" AND [Quantity]<0 OR [Type] Like "Buy*" AND [Quantity]>0 into the validation rule with"wrong" in text
entered record 1
Sell
attempt to enter Quantity 1 received "wrong" changed to -1 and record inserted to record 2
entered Sell to Open record 2
attempt to enter 11 received "wrong" changed to -11 and record added.

Previous Db, in table "Trades" (7291 records) had errors in data when inserting identical validation rule.
Why the different behaviors? Is the error generated by improper signage, the occurrence I'm attempting to eliminate. That being the case I have to query the records and see/change any issues.

[bknight]

Well I did a bit of investigation. It seems that I have use type over the last 12 years to some different types than I previously commented on.

[Type] Like "Sell*" AND [QtyFilled]<0 OR [Type] Like "Buy*" AND [QtyFilled]>0. This is going to be a rather complex validation.
I want to eliminate all those types that aren't Sell* or Buy* from any validation rule such as "Assigned", Part*, Dividend, Cash, Fee. I have eliminated the Null field so that isn't an issue.
[Type] Like "Sell*" AND [QtyFilled]<0 OR [Type] Like "Buy*" AND [QtyFilled]>0 AND [Type] Not Like "Ass*" AND Not Like "Part*" AND Not Like "Div*" AND Not Like "Cash" AND Like "Fee"
Does this look correct? I ask because I wish to have a reasonable chance of success without errors.

[HansV]

Do you want to allow any value in QtyFilled if Type is anything else than Sell* or Buy*?
Or do you want to disallow anything but Sell* and Buy*?
Or something else?

[bknight]

Assigned should have a positive, Cash will be Null, Div* will be Null, Exp* will be positive or negative, Fee will be null, Part* will be null. Those are the results of the queries for the last 12 years.

[bknight]

Here are examples of Exp*

[HansV]

Perhaps

[Type] Like "Sell*" AND [QtyFilled]<0 OR ([Type] Like "Buy*"] OR [Type]="Assigned") AND [QtyFilled]>0 OR ([Type]="Cash" OR [Type] Like "Div*" OR [Type]="Fee" OR [Type] Like "Part*") AND [QtyFilled] Is Null OR [Type] Like "Exp*"

[bknight]

Looks reasonable, I'm headed to dinner and I'll post after I put it to use, but you not see it until tomorrow.

[bknight]

[Type] Like "Sell*" AND [QtyFilled]<0 OR ([Type] Like "Buy*"] OR [Type]="Assigned") AND [QtyFilled]>0 OR ([Type]="Cash" OR [Type] Like "Div*" OR [Type]="Fee" OR [Type] Like "Part*") AND [QtyFilled] Is Null OR [Type] Like "Exp*" Ther was one "]" after Like "Buy*" it didn't compile. Removed same.
Activated and indicated some data changed. I did a cursory data review aand seemed OK.
Thanks

[bknight]

The positions form found four positions that had changed and I found them and corrected the errors, now only active positions show on that form, so it was a good idea to finally put in the validation rule.