I need the quantity entered in a field to be <= a certain quantity. I've opened the field properties and entered the following under the "Validation Rule".
<= ( sum ( ( [T - Main Frame]![Quantity] ) Where ( ( [T - Main Frame]![Part #] = [Part #] ) AND ( [T - Main Frame]![MSO #] = [MSO #] ) AND ( [T - Main Frame]![Status] = "Received" Or "Shipped" Or "Scrapped" ) ) ) )
I get the error message:
"The expression you entered contains invalid syntax."
"You may have entered an operand without an operator."
Any ideas or additional info needed?
8  4474 
Hi. You are trying to OR values together for your [Status] field test as you would in saying them - but you must specify the field each time. Try: - ... AND (([T - Main Frame]![Status] = "Received") Or ([T - Main Frame]![Status] = "Shipped") Or ([T - Main Frame]![Status] = "Scrapped"))
You are also using a WHERE clause which is invalid in this context.
In any event I doubt very much that form control names are valid in the validation rule of a field. If this turns out to be the case you will need to do the error checking within the form itself, using a VBA subroutine in the form control's Before Update event.
I strongly suggest you look up the help info on validation rules.
Given that your WHERE clause is not appropriate for the type of validation you are trying, you are ORing the wrong things, and you are using form control references where these may not be allowed, I am sure that there is so much wrong with what you are trying right now that you need to rethink this one from the beginning.
-Stewart
Hi. You are trying to OR values together for your [Status] field test as you would in saying them - but you must specify the field each time. Try:
- ... AND (([T - Main Frame]![Status] = "Received") Or ([T - Main Frame]![Status] = "Shipped") Or ([T - Main Frame]![Status] = "Scrapped"))
You are also using a WHERE clause which is invalid in this context.
In any event I doubt very much that form control names are valid in the validation rule of a field. If this turns out to be the case you will need to do the error checking within the form itself, using a VBA subroutine in the form control's Before Update event.
I strongly suggest you look up the help info on validation rules.
Given that your WHERE clause is not appropriate for the type of validation you are trying, you are ORing the wrong things, and you are using form control references where these may not be allowed, I am sure that there is so much wrong with what you are trying right now that you need to rethink this one from the beginning.
-Stewart
I reconstructed my expression as a sum query and then used the results of the query for the validation which still doesn't work. The new expression simply states:
<=[Q - Limit Quantity]![SumOfQuantity]
When I test I get an error "MSAccess can't parse the Validation Rule expression you entered".
What am I doing wrong?
Hi. Validation rules will not accept query references, form field references and so on. You can compare the value of one field in the table to another, but you cannot reference values from a query.
If you look up the help entries for Validation Rule as previously suggested you will see what is possible.
The most flexible solution is to use VBA code to validate a field, as you are then free of the restrictions that stop validation rules from being anything other than simple comparisons of one table field against another.
-Stewart
NeoPa 32,556
Expert Mod 16PB
Please don't double-post your questions. This wastes the time of those people who offer their time for free to try to help you.
I can see that you're new here today (yesterday), so I'll leave this as a request for you to review our rules ( Help). I won't be as lenient if this happens again.
The other thread is found at Inventory Problem - Field Validation.
Please don't double-post your questions. This wastes the time of those people who offer their time for free to try to help you.
I can see that you're new here today (yesterday), so I'll leave this as a request for you to review our rules (Help). I won't be as lenient if this happens again.
The other thread is found at Inventory Problem - Field Validation.
Please note that I deleted my second question shortly after posting because I noticed it failed to meet the requirements of the specific rule you mention. My apologies for any inconvenice this caused. Thanks for your reminder though.
Hi. Validation rules will not accept query references, form field references and so on. You can compare the value of one field in the table to another, but you cannot reference values from a query.
If you look up the help entries for Validation Rule as previously suggested you will see what is possible.
The most flexible solution is to use VBA code to validate a field, as you are then free of the restrictions that stop validation rules from being anything other than simple comparisons of one table field against another.
-Stewart
Appreciate the advise. I read where you can create a query in design mode and then copy the SQL statement to code. I'll post if I get it to work. Much appreciation.
NeoPa 32,556
Expert Mod 16PB
Please note that I deleted my second question shortly after posting because I noticed it failed to meet the requirements of the specific rule you mention. My apologies for any inconvenice this caused. Thanks for your reminder though.
I can only assume that you did actually attempt this, as the thread is clearly still there. As an ordinary member this would not be possible for you after an hour had elapsed since posting.
As there are now responses in the thread, I won't delete it.
Not a problem, from what you say I doubt this is a topic I need worry about for the future ;)
I can only assume that you did actually attempt this, as the thread is clearly still there. As an ordinary member this would not be possible for you after an hour had elapsed since posting.
As there are now responses in the thread, I won't delete it.
Not a problem, from what you say I doubt this is a topic I need worry about for the future ;)
Funny, I opened my user profile and deleted it from there. ? I double checked I deleted it from subscriptions instead of started. Thanks for your understanding.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: c++newbie |
last post by:
Hi all,
I try to compile the following classes:
main.cpp:
#include <algorithm>
#include <iostream>
#include <fstream>
#include <iterator>
|
by: John Øllgård Jensen |
last post by:
Hi
Using MS Asccess 2000:
In a query I'm trying to create a new field with following expression:
FilmDate: Left(,4)
The field "FilmNo" is another text field in the query.
This is...
|
by: linq936 |
last post by:
Hi,
I have this piece code,
struct TriStr {
MyString str1;
MyString str2;
MyString str3;
TriStr(MyString s1, MyString s2, MyString s3){
this->str1 = s1;
|
by: nescio |
last post by:
hello,
i am making a form using php/html/javascript
a part of the form is (email address) comming from a database.
the amount of addresses is always different.
every address has a checkbox....
|
by: chrisstankevitz |
last post by:
Hi,
Q1: Is there a way to make a template function that works only for
specific types which produces a compiler error if used with an invalid
type?
Q2: If not, how do people deal with this...
|
by: yalbizu |
last post by:
#include <iostream>
#include <string>
#include <fstream>
#include <iomanip>
using namespace std;
const int NO_OF_STUDENTS=20;
struct studentType
{
string studentFName;
string studentLName;
|
by: Stang02GT |
last post by:
I have been asked to validate a date on our web-page so that people cannot enter dates like 14/1/08 or 2/30/06. I have found code that will do exactly what i need it to do, but i am not sure how to...
|
by: Kapteyn's Star |
last post by:
Hi newsgroup
The program here given is refused by GCC with a error i cannot
understand. It says
rnd00.c: In function ‘main’:
rnd00.c:26: error: expected expression before ‘]’ token
...
|
by: markryde |
last post by:
Hello,
Followed here is a simplified code example of something which I
try to implement; in essence , I want to assign a value to a return
value of a method is C. I know, of course, that in this...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
