Re: How to use an IFF statement for validation rule
- From: PAULinLAOS <PAULinLAOS@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 25 Sep 2007 18:00:00 -0700
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" wrote:
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"PAULinLAOS" <PAULinLAOS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:405D541D-C740-4F49-B359-8225B9396A2B@xxxxxxxxxxxxxxxx
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?
- References:
- Re: How to use an IFF statement for validation rule
- From: Allen Browne
- Re: How to use an IFF statement for validation rule
- Prev by Date: Re: drop down list stopped working
- Next by Date: Autocomplete email domain from previous record
- Previous by thread: Re: How to use an IFF statement for validation rule
- Next by thread: Record counter in navigation bar problem !! !!
- Index(es):
Relevant Pages
|
