By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,046 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Validation Error - Invalid Syntax - Operand w/o operator

P: 12
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?
Sep 15 '08 #1
Share this Question
Share on Google+
8 Replies


Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. ... 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
Sep 15 '08 #2

P: 12
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:

Expand|Select|Wrap|Line Numbers
  1. ... 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?
Sep 15 '08 #3

Expert Mod 2.5K+
P: 2,545
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
Sep 15 '08 #4

NeoPa
Expert Mod 15k+
P: 31,347
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.
Sep 16 '08 #5

P: 12
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.
Sep 16 '08 #6

P: 12
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.
Sep 16 '08 #7

NeoPa
Expert Mod 15k+
P: 31,347
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 ;)
Sep 16 '08 #8

P: 12
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.
Sep 16 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.