I have made a very simple query that calculates a field called qty_to_order.
The 2 fields that create the calucaltion is Maintenance - Physical. The
formula works really well for what I need it to do until the answer is 0.
Then I get an error message that says I violate a validation rule in the main
table. Now I do not have the calculation going to the main table. (I know
this can't be done). If the answer is 0 how can I proceed on to the next
record?


Thanks for any help anyone can give

Tara

Tara,

What is the Validation Rule in the main table that is being violated
here? Maybe the problem is not directly related to the calculation, or
the result thereof. If the "answer" is 0, then this presumably means
that Maintenance=Physical, so maybe there is a validation rule...
[Maintenance]>[Physical]
.... or something else related to the data, so whatever the validation
rule is, it will need to be adjusted to cater to the real data rules.

--
Steve Schapel, Microsoft Access MVP

ktfrubel wrote:
> I have made a very simple query that calculates a field called qty_to_order.
> The 2 fields that create the calucaltion is Maintenance - Physical. The
> formula works really well for what I need it to do until the answer is 0.
> Then I get an error message that says I violate a validation rule in the main
> table. Now I do not have the calculation going to the main table. (I know
> this can't be done). If the answer is 0 how can I proceed on to the next
> record?
>
>
> Thanks for any help anyone can give
>
> Tara

Steve,
Thank you so much for responding. I have looked and relooked in the main
table, and I have never set any validation rules. I have tried deleting and
re-entering the fields to make sure there were no validation rules. I have
them set to a "number field" I even had my supervisor who is an Access guru,
look at it and she can't figure it out. Maybe if I explaine what I am doing
it could make it a bit more clear.
I have had to create an inventory database (the template provided by access
is a little more than we need right now.). I have 2 number fields
Qty_to_Maintain and Phyical_Count. In my query I have created a field called
Amount to Order: [qty_to_maintain]-[Physical_Count]. Everything is great if
the answer is not 0. If it is 3 to manintain and 2 Phyical=1 to order. If
maint is 4 and we have 5 then -1 is the answer. But if maint is 3 and on
hand is 3 the answer is 0, and that is where the problem begins.

thanks for your help

"Steve Schapel" wrote:

> Tara,
>
> What is the Validation Rule in the main table that is being violated
> here? Maybe the problem is not directly related to the calculation, or
> the result thereof. If the "answer" is 0, then this presumably means
> that Maintenance=Physical, so maybe there is a validation rule...
> [Maintenance]>[Physical]
> .... or something else related to the data, so whatever the validation
> rule is, it will need to be adjusted to cater to the real data rules.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> ktfrubel wrote:
> > I have made a very simple query that calculates a field called qty_to_order.
> > The 2 fields that create the calucaltion is Maintenance - Physical. The
> > formula works really well for what I need it to do until the answer is 0.
> > Then I get an error message that says I violate a validation rule in the main
> > table. Now I do not have the calculation going to the main table. (I know
> > this can't be done). If the answer is 0 how can I proceed on to the next
> > record?
> >
> >
> > Thanks for any help anyone can give
> >
> > Tara
>

Tara,

Thanks for the further explanation. However, there must be an
explanation for this behaviour. Can you please let us know the exact
wording of the error message you receive? And can you also say *when*
you receive this error message?

If you go to the design view of the table, select Properties from the
View menu. Can you confirm that there is no Validation Rule entered there?

--
Steve Schapel, Microsoft Access MVP

ktfrubel wrote:
> Steve,
> Thank you so much for responding. I have looked and relooked in the main
> table, and I have never set any validation rules. I have tried deleting and
> re-entering the fields to make sure there were no validation rules. I have
> them set to a "number field" I even had my supervisor who is an Access guru,
> look at it and she can't figure it out. Maybe if I explaine what I am doing
> it could make it a bit more clear.
> I have had to create an inventory database (the template provided by access
> is a little more than we need right now.). I have 2 number fields
> Qty_to_Maintain and Phyical_Count. In my query I have created a field called
> Amount to Order: [qty_to_maintain]-[Physical_Count]. Everything is great if
> the answer is not 0. If it is 3 to manintain and 2 Phyical=1 to order. If
> maint is 4 and we have 5 then -1 is the answer. But if maint is 3 and on
> hand is 3 the answer is 0, and that is where the problem begins.

Steve,
Again thank you for responding. I messed with this table all day on Friday,
and could not find any where where the validation rule was written. So I
deleted the table and created it again. And now everything is working how it
should. So that is telling me that there was something wrong in the main
table, but for the life of me I coudn't find it.
Thank you for your help, and If I need any further help, I will post it here.



Thanks

"Steve Schapel" wrote:

> Tara,
>
> Thanks for the further explanation. However, there must be an
> explanation for this behaviour. Can you please let us know the exact
> wording of the error message you receive? And can you also say *when*
> you receive this error message?
>
> If you go to the design view of the table, select Properties from the
> View menu. Can you confirm that there is no Validation Rule entered there?
>
> --
> Steve Schapel, Microsoft Access MVP
>
> ktfrubel wrote:
> > Steve,
> > Thank you so much for responding. I have looked and relooked in the main
> > table, and I have never set any validation rules. I have tried deleting and
> > re-entering the fields to make sure there were no validation rules. I have
> > them set to a "number field" I even had my supervisor who is an Access guru,
> > look at it and she can't figure it out. Maybe if I explaine what I am doing
> > it could make it a bit more clear.
> > I have had to create an inventory database (the template provided by access
> > is a little more than we need right now.). I have 2 number fields
> > Qty_to_Maintain and Phyical_Count. In my query I have created a field called
> > Amount to Order: [qty_to_maintain]-[Physical_Count]. Everything is great if
> > the answer is not 0. If it is 3 to manintain and 2 Phyical=1 to order. If
> > maint is 4 and we have 5 then -1 is the answer. But if maint is 3 and on
> > hand is 3 the answer is 0, and that is where the problem begins.
>