Validation based on another fields value

[Touche'' Techie]

I have an ERP solution that I am hardening.
In an inventory transaction form there is a field in which you select if the
transaction is a sale, rec'd, return, etc. In another field you enter the #
in the transation. I want a validation rule checking that if the transaction
is "sold" that the # in the transaction is entered as negative, rec'd =
positive... etc.
My thoughts were something like this for a validation rule:

(< 0 and ([Transaction Type]=IN("Sold","Shrinkage")))or(>=0 and
([Transaction Type]=IN("Begin","Return","Rec'd")))or(<>0 and ([Transaction
Type]=IN("Inventory")))

This throws an error of invalid syntax, but I don't know if I am way off
base on how I am doing this or if am I missing something minor?

 

[NetworkTrade]

Should one presume that your code would be put in the 'AfterUpdate' event of
the transaction field? Then it would be an if/then statement and reference
the transaction type field....plus probably generating a MessageBox for an
incorrect state...

Not sure where your code is going and what trigger makes it active....

 

[Touche'' Techie]

I was planning on just placing the validation code in the
transaction#/data/validation rule box. With a message in the validation text
box if validation fails.
I'm not sure what if/then statement you are suggesting. I don't think you
are suggesting that basically if sold then change the number to negative and
store that value to the table.
Are you suggesting something more like if sold and >0 or rec'd and <0 then
throw an error message?
I am trying to catch entry mistakes as it is easy in this DB structure to
enter something as sold but then forget to enter it as negative.
Unfortunately this is a bit of a work around for existing data where all
inventory is in one data column and pos. and neg. entries are essential to
accurate inventory reports.

Thanks

Should one presume that your code would be put in the 'AfterUpdate' event of
the transaction field? Then it would be an if/then statement and reference
the transaction type field....plus probably generating a MessageBox for an
incorrect state...

Not sure where your code is going and what trigger makes it active....

--
NTC

I have an ERP solution that I am hardening.
In an inventory transaction form there is a field in which you select if the
transaction is a sale, rec'd, return, etc. In another field you enter the #
in the transation. I want a validation rule checking that if the transaction
is "sold" that the # in the transaction is entered as negative, rec'd =
positive... etc.
My thoughts were something like this for a validation rule:

(< 0 and ([Transaction Type]=IN("Sold","Shrinkage")))or(>=0 and
([Transaction Type]=IN("Begin","Return","Rec'd")))or(<>0 and ([Transaction
Type]=IN("Inventory")))

This throws an error of invalid syntax, but I don't know if I am way off
base on how I am doing this or if am I missing something minor?

 

[Allen Browne]

Since you are comparing fields, you need to put this in the Validation Rule
of *table*, not of a field.

Open your table in design view.
Open the Properties box (View menu.)
Beside the Validation Rule in the Properties box, enter something like this,
replacing "f1" with the name of your field:

([F1]< 0 and [Transaction Type] IN ("Sold","Shrinkage"))
OR ([F1] >= 0 and [Transaction Type] IN ("Begin","Return","Rec'd"))
OR ([F1] <> 0 and [Transaction Type] IN ("Inventory"))

You will notice several changes there, e.g.
- field name is included;
- you don't use = with IN.

Presumably [Transaction Type] is a Text field (not a Number field that just
shows text in a combo.)

Another way to solve this problem might be to create a little lookup table
for the transaction types, with a Number field that indicates the direction
of the transaction. Use 1 for stuff coming into stock, and -1 for stuff
being removed from stock, e.g:
TransactionTypeID Direction
Begin 1
Sold -1
Recd 1
You can now create a query that works out the quantities without requiring
the user to type in negative values. The query has both tables, so the net
quantity is:
[Direction] * [Quantity]

 

[Touche'' Techie]

Thanks, the table validation does seem as though it will work fine. I need
the client to reconcile some entries because they fail the validation at
table level, but I'm sure it will work as it should once I get this old data
cleaned up

([Units in Transaction]<0 AND [Transaction Type] IN ("Sold","Shrinkage")) OR
([Units in Transaction]>=0 AND [Transaction Type] IN
("Begin","Return","Rec'd")) OR ([Units in Transaction]<>0 AND [Transaction
Type] IN ("Inventory"))

Transaction Type is a combo box text.

Known drawbacks for this method is that as transaction types change
(unlikely but possible.. i.e. add an Employee sale transaction type), this
validation becomes invalid and would need to be edited.

I had considered something along the lines of the second way when I started
into this project and thought it would be a cleaner design. The old imported
data had the pos. and negative numbers in transaction in one column and I
thought, at that time, that it would be easier to continue with that format
rather than converting all the negatives to pos. (although this wouldn't take
long). In some ways it has been good that I had not converted the numbers as
I am still finding old data entry errors. The transaction type is, in fact,
not immediately useful information (other than as a validator) and
unnecessary for inventory, but as the data gets cleaned up I may switch to
this method or use the transaction types in some report.

Thanks Allen!

Since you are comparing fields, you need to put this in the Validation Rule
of *table*, not of a field.

Open your table in design view.
Open the Properties box (View menu.)
Beside the Validation Rule in the Properties box, enter something like this,
replacing "f1" with the name of your field:

([F1]< 0 and [Transaction Type] IN ("Sold","Shrinkage"))
OR ([F1] >= 0 and [Transaction Type] IN ("Begin","Return","Rec'd"))
OR ([F1] <> 0 and [Transaction Type] IN ("Inventory"))

You will notice several changes there, e.g.
- field name is included;
- you don't use = with IN.

Presumably [Transaction Type] is a Text field (not a Number field that just
shows text in a combo.)

Another way to solve this problem might be to create a little lookup table
for the transaction types, with a Number field that indicates the direction
of the transaction. Use 1 for stuff coming into stock, and -1 for stuff
being removed from stock, e.g:
TransactionTypeID Direction
Begin 1
Sold -1
Recd 1
You can now create a query that works out the quantities without requiring
the user to type in negative values. The query has both tables, so the net
quantity is:
[Direction] * [Quantity]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an ERP solution that I am hardening.
In an inventory transaction form there is a field in which you select if
the
transaction is a sale, rec'd, return, etc. In another field you enter the
#
in the transation. I want a validation rule checking that if the
transaction
is "sold" that the # in the transaction is entered as negative, rec'd =
positive... etc.
My thoughts were something like this for a validation rule:

(< 0 and ([Transaction Type]=IN("Sold","Shrinkage")))or(>=0 and
([Transaction Type]=IN("Begin","Return","Rec'd")))or(<>0 and ([Transaction
Type]=IN("Inventory")))

This throws an error of invalid syntax, but I don't know if I am way off
base on how I am doing this or if am I missing something minor?