Access 2007 If Statement in a Validation Rule

ms-access if-statement validationrules
user127181 picture user127181 · Apr 3, 2012 · Viewed 7.9k times · Source

I have an Inventory Database and I need to add a validation rule to the quantity field when the user inputs the amount of an item into the system. I want it to stop the user if he is inputting a # greater then that which is currently in stock (calculated field). I only want this to happen when inventory is going out (Outgoing Transaction or shrinkage etc...) but not during an incoming transaction (Order Entry).

This is the rule that I've applied to the quantity field in the form but it always delivers the validation message so I'm doing something wrong.

=IIf([Forms]![ALL ORDERS]![Transaction Type]=1,>0,<=[Text9])

[Transaction Type] 1 is an Incoming order so any # greater then 0 is accepted, but if the transaction type is any other kind, then the quantity has to be less then [Text9] (current stock).

What and I doing wrong???

Thanks

Answer

Fionnuala picture Fionnuala · Apr 3, 2012

This seems to work for me:

([Transaction Type]=1 And [Quantity]>0) Or ([Transaction type]>1 And [Quantity]<=[Text9])

Note, no equals.

Related questions

How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want …

Read More
How to SUM two fields within an SQL query

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row. This is my code. Sum(tbl1.fld1 + tbl1.fld2) AS [Total] …

Read More
Code to loop through all records in MS Access

I need a code to loop through all the records in a table so I can extract some data. In addition to this, is it also possible to loop through filtered records and, again, extract data? Thanks!

Read More