How to use an IFF statement for validation rule

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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.
 
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.

Allen Browne said:
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.

PAULinLAOS said:
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?
Click to expand...
Click to expand...
 
Back
Top