Validation Error - Invalid Syntax - Operand w/o operator

[glitke]

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?

[Stewart Ross]

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

[glitke]

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?

[Stewart Ross]

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]

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.

[glitke]

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.

[glitke]

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]

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 ;)

[glitke]

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.