Calculation not accepted if value is 0

[Guest]

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 Schapel]

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.

 

[Guest]

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

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

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 Schapel]

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?

 

[Guest]

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

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

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.