How to use an IFF statement for validation rule

[Guest]

I want to validate data entry in one control based on another. It's an
inventory database. In one control, I choose from a combo box called
[Combo18] whether the record is a 'Sale' or a 'Purchase'. 'Sale'/'Purchase'
is stored in the control [type] as a number, Sale=1 and Purchase=2. I also
have one field/control for [IN], which is a number/amount for how many items
were purchased. So, the rule is that if the combo box shows 'Purchase', then
[IN] should be an integer of 1 or more. If it's a sale, then [IN] is zero.

I've tried, unsuccessfully, at writing a validation rule in the properties
of the [IN] text box on the form as follows:

IIf([type]=2,>0,0)

I also tried:

IIf(Forms![inventory]![Combo18]="Purchase",>0,0)

Neither of these work. Any idea of how to write this simple IF/THEN
statement to validate my data entry?

 

[Allen Browne]

1. Open the table in design view.

2. Open the Properties box (View menu.)

3. Beside the Validation Rule in the Properties box, enter something like
this:
([TransType] = 1 AND [In] = 0) OR ([TransType] = 2 AND [In] > 0)

Replace TransType with the name of the field that the combo is bound to.

Notes:
====
a) Since you are comparing fields, you must use the rule in the properties
box, not the one in the lower pane of table design (which is the rule for a
field.)

b) The bracketing is important when mixing ANDs and ORs.

c) Using IN as a field name could cause you problems, since it is a reserved
word in JET SQL. Here's a list you can refer to when designing tables:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html

d) Your IN field is for a quantity. You might want to consider whether there
could be cases where a sale is for a quantity also.

e) If the sale quantity must always be zero, the TransType might be
superflous (i.e. IN = 0 could be the indicator for a sale.)

f) If you do have a Quantity field that is used for both purchases and
sales, you might use -1 = Sale and 1 = Purchase. This would allow you to sum
the expression:
SaleType * Quantity
for the net difference for each product.

 

[Guest]

Wow, Allen, that's great. It works nicely. Actually, I have two columns, one
for inventory 'IN' and one for inventory 'OUT'. So, I added on to the rule to
take care of the OUT column, too, following the example you gave.

I also tried this on the text boxes in the form and it also works. Thanks a
bunch. It was simpler than I thought. No need for an If-Then statement.

1. Open the table in design view.

2. Open the Properties box (View menu.)

3. Beside the Validation Rule in the Properties box, enter something like
this:
([TransType] = 1 AND [In] = 0) OR ([TransType] = 2 AND [In] > 0)

Replace TransType with the name of the field that the combo is bound to.

Notes:
====
a) Since you are comparing fields, you must use the rule in the properties
box, not the one in the lower pane of table design (which is the rule for a
field.)

b) The bracketing is important when mixing ANDs and ORs.

c) Using IN as a field name could cause you problems, since it is a reserved
word in JET SQL. Here's a list you can refer to when designing tables:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html

d) Your IN field is for a quantity. You might want to consider whether there
could be cases where a sale is for a quantity also.

e) If the sale quantity must always be zero, the TransType might be
superflous (i.e. IN = 0 could be the indicator for a sale.)

f) If you do have a Quantity field that is used for both purchases and
sales, you might use -1 = Sale and 1 = Purchase. This would allow you to sum
the expression:
SaleType * Quantity
for the net difference for each product.

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

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

I want to validate data entry in one control based on another. It's an
inventory database. In one control, I choose from a combo box called
[Combo18] whether the record is a 'Sale' or a 'Purchase'.
'Sale'/'Purchase'
is stored in the control [type] as a number, Sale=1 and Purchase=2. I also
have one field/control for [IN], which is a number/amount for how many
items
were purchased. So, the rule is that if the combo box shows 'Purchase',
then
[IN] should be an integer of 1 or more. If it's a sale, then [IN] is zero.

I've tried, unsuccessfully, at writing a validation rule in the properties
of the [IN] text box on the form as follows:

IIf([type]=2,>0,0)

I also tried:

IIf(Forms![inventory]![Combo18]="Purchase",>0,0)

Neither of these work. Any idea of how to write this simple IF/THEN
statement to validate my data entry?