Condition and equation problems

[Ed]

So, I'm making a query being used to generate a report to use as order
forms for inventory counts. This query takes data from the inventory
count, analyzes it and is *supposed* to generate a list of items with
less than a specified on-hand minimum. Right now, the fields I have in
the query are:

Item ID
Item Description
Production Unit of Measure (Units the warehoude is to count by. ie,
feet)
Quantity On Hand (This field sums all the counts for the Item ID from
each location it is counted in in Production Units. It's set with the
condition "Is Not Null" since not all items are counted in an
inventory count.)
Vendor Unit of Measure (ie, coils)
Vendor Units On Hand ([Quantity On Hand]/[Amount per Unit])
Amount per Unit(ie, feet per coil)
Minimum In Stock (In Vendor Units)
Maximum In Stock (In Vendor Units)
Amount To Order (Amount, in vendor units, needed to bring Quantity On
Hand up to Max In Stock level rounded up to nearest whole number (-Int
(-1*(([Max in Stock]*[Amt Per Unit])-[SumOfQuantity On Hand])/[Amt Per
Unit])
Minimum Order Requirement
Unit Cost

Here are the problems I'm running in to. When I run the query, a
parameter box comes up for "SumOfQuantity On Hand". If I erase the
"Vendor Units On Hand" field this paramater box doesn't show up. If I
click OK with or without entering anything into the box, the query
runs ad all the values are fine. I think what's happening is that the
query is looking for the value of "SumOfQuantity On Hand" before the
sum runs. How do I stop this box from showing up?

The amount to order box will sometimes generate negative values, when
we have more than the minimum on hand. I try to put a condition in
(">0") but then no records show up in the query. What am I doing
wrong?

Last, is there a way to default the value for "Amount to Order" to the
"Minimum Order Requirement" if the amount to order will be less than
it?

Thanks so much in advance for your help. This is my first Access
project outside a classroom and I'm a little lost without the step-by-
step textbook lessons.

 

[-james]

The synatax for "Quantity On Hand" is incorrect. Copy and paste your field
here EXACTLY as it's in the query and I'll help you figure it out.

 

[KARL DEWEY]

I think what's happening is that the query is looking for the value of
"SumOfQuantity On Hand" before the sum runs. How do I stop this box from
showing up?
Use the orignal calculations instead of [SumOfQuantity On Hand] in the
formula.
Use and IIF statement --
IIF( Your formula <0, 0, Your formula) AS [Amount to order]
"Minimum Order Requirement" if the amount to order will be less than
it?
UNTESTED ----
IIF([Minimum Order Requirement] >0 AND (IIF( Your formula <0, 0, Your
formula)) < [Minimum Order Requirement]), [Minimum Order Requirement], IIF(
Your formula <0, 0, Your formula) AS [Amount to order]

[Minimum Order Requirement]
--
KARL DEWEY
Build a little - Test a little

So, I'm making a query being used to generate a report to use as order
forms for inventory counts. This query takes data from the inventory
count, analyzes it and is *supposed* to generate a list of items with
less than a specified on-hand minimum. Right now, the fields I have in
the query are:

Item ID
Item Description
Production Unit of Measure (Units the warehoude is to count by. ie,
feet)
Quantity On Hand (This field sums all the counts for the Item ID from
each location it is counted in in Production Units. It's set with the
condition "Is Not Null" since not all items are counted in an
inventory count.)
Vendor Unit of Measure (ie, coils)
Vendor Units On Hand ([Quantity On Hand]/[Amount per Unit])
Amount per Unit(ie, feet per coil)
Minimum In Stock (In Vendor Units)
Maximum In Stock (In Vendor Units)
Amount To Order (Amount, in vendor units, needed to bring Quantity On
Hand up to Max In Stock level rounded up to nearest whole number (-Int
(-1*(([Max in Stock]*[Amt Per Unit])-[SumOfQuantity On Hand])/[Amt Per
Unit])
Minimum Order Requirement
Unit Cost

Here are the problems I'm running in to. When I run the query, a
parameter box comes up for "SumOfQuantity On Hand". If I erase the
"Vendor Units On Hand" field this paramater box doesn't show up. If I
click OK with or without entering anything into the box, the query
runs ad all the values are fine. I think what's happening is that the
query is looking for the value of "SumOfQuantity On Hand" before the
sum runs. How do I stop this box from showing up?

The amount to order box will sometimes generate negative values, when
we have more than the minimum on hand. I try to put a condition in
(">0") but then no records show up in the query. What am I doing
wrong?

Last, is there a way to default the value for "Amount to Order" to the
"Minimum Order Requirement" if the amount to order will be less than
it?

Thanks so much in advance for your help. This is my first Access
project outside a classroom and I'm a little lost without the step-by-
step textbook lessons.

 

[Ed]

The synatax for "Quantity On Hand" is incorrect. Copy and paste your field
here EXACTLY as it's in the query and I'll help you figure it out.

So, I'm making a query being used to generate a report to use as order
forms for inventory counts. This query takes data from the inventory
count, analyzes it and is *supposed* to generate a list of items with
less than a specified on-hand minimum. Right now, the fields I have in
the query are:

Item ID
Item Description
Production Unit of Measure (Units the warehoude is to count by. ie,
feet)
Quantity On Hand (This field sums all the counts for the Item ID from
each location it is counted in in Production Units. It's set with the
condition "Is Not Null" since not all items are counted in an
inventory count.)
Vendor Unit of Measure (ie, coils)
Vendor Units On Hand ([Quantity On Hand]/[Amount per Unit])
Amount per Unit(ie, feet per coil)
Minimum In Stock (In Vendor Units)
Maximum In Stock (In Vendor Units)
Amount To Order (Amount, in vendor units, needed to bring Quantity On
Hand up to Max In Stock level rounded up to nearest whole number (-Int
(-1*(([Max in Stock]*[Amt Per Unit])-[SumOfQuantity On Hand])/[Amt Per
Unit])
Minimum Order Requirement
Unit Cost

Here are the problems I'm running in to. When I run the query, a
parameter box comes up for "SumOfQuantity On Hand". If I erase the
"Vendor Units On Hand" field this paramater box doesn't show up. If I
click OK with or without entering anything into the box, the query
runs ad all the values are fine. I think what's happening is that the
query is looking for the value of "SumOfQuantity On Hand" before the
sum runs. How do I stop this box from showing up?

The amount to order box will sometimes generate negative values, when
we have more than the minimum on hand. I try to put a condition in
(">0") but then no records show up in the query. What am I doing
wrong?

Last, is there a way to default the value for "Amount to Order" to the
"Minimum Order Requirement" if the amount to order will be less than
it?

Thanks so much in advance for your help. This is my first Access
project outside a classroom and I'm a little lost without the step-by-
step textbook lessons.- Hide quoted text -

- Show quoted text -

I'm not using an equation for this. The way I have the tables set up
is I have one table with a record for each item, and a second table
with a record for each location each item is in. When inventory is
counted, each location does it's own count. I am attempting to total
each location count to a grand total. I think the problem has to do
with the Vendor Units On Hand, where I'm trying to do an equation
using the total the Quantity On Hand total because when I delete the
Vendor Units On Hand field, everything comes up fine.

The equation for Vendor Units On Hand is: [SumOfQuantity On Hand]/[Amt
Per Unit]

 

[Ed]

The synatax for "Quantity On Hand" is incorrect. Copy and paste your field
here EXACTLY as it's in the query and I'll help you figure it out.

So, I'm making a query being used to generate a report to use as order
forms for inventory counts. This query takes data from the inventory
count, analyzes it and is *supposed* to generate a list of items with
less than a specified on-hand minimum. Right now, the fields I have in
the query are:
Item ID
Item Description
Production Unit of Measure (Units the warehoude is to count by. ie,
feet)
Quantity On Hand (This field sums all the counts for the Item ID from
each location it is counted in in Production Units. It's set with the
condition "Is Not Null" since not all items are counted in an
inventory count.)
Vendor Unit of Measure (ie, coils)
Vendor Units On Hand ([Quantity On Hand]/[Amount per Unit])
Amount per Unit(ie, feet per coil)
Minimum In Stock (In Vendor Units)
Maximum In Stock (In Vendor Units)
Amount To Order (Amount, in vendor units, needed to bring Quantity On
Hand up to Max In Stock level rounded up to nearest whole number (-Int
(-1*(([Max in Stock]*[Amt Per Unit])-[SumOfQuantity On Hand])/[Amt Per
Unit])
Minimum Order Requirement
Unit Cost
Here are the problems I'm running in to. When I run the query, a
parameter box comes up for "SumOfQuantity On Hand". If I erase the
"Vendor Units On Hand" field this paramater box doesn't show up. If I
click OK with or without entering anything into the box, the query
runs ad all the values are fine. I think what's happening is that the
query is looking for the value of "SumOfQuantity On Hand" before the
sum runs. How do I stop this box from showing up?
The amount to order box will sometimes generate negative values, when
we have more than the minimum on hand. I try to put a condition in
(">0") but then no records show up in the query. What am I doing
wrong?
Last, is there a way to default the value for "Amount to Order" to the
"Minimum Order Requirement" if the amount to order will be less than
it?
Thanks so much in advance for your help. This is my first Access
project outside a classroom and I'm a little lost without the step-by-
step textbook lessons.- Hide quoted text -

- Show quoted text -

I'm not using an equation for this. The way I have the tables set up
is I have one table with a record for each item, and a second table
with a record for each location each item is in. When inventory is
counted, each location does it's own count. I am attempting to total
each location count to a grand total. I think the problem has to do
with the Vendor Units On Hand, where I'm trying to do an equation
using the total the Quantity On Hand total because when I delete the
Vendor Units On Hand field, everything comes up fine.

The equation for Vendor Units On Hand is: [SumOfQuantity On Hand]/[Amt
Per Unit]- Hide quoted text -

- Show quoted text -

The epuation I'm using now for the Amount To Order field is:
AmountToOrder: IIf(-Int(-1*(([Max in Stock]*[Amt Per Unit])-
[SumOfQuantity On Hand])/[Amt Per Unit])<[Minimum Order Requirement],
[Minimum Order Requirement]/[Amt Per Unit],-Int(-1*(([Max in Stock]*
[Amt Per Unit])-[SumOfQuantity On Hand])/[Amt Per Unit]))

It works perfectly, thanks.

I'm still working on the issue with the filtering out of items that
have more in stock than the minimum we need. In these situations the
equation which calculates the amount we need to order (above) will
generate a negative value. I put a >0 on the first condition line
(along with 2 other conditions) and all of the records still show up.
If I put it on an Or line no records show up.

Also, I still can't figure out how to stop the prompt for the
"SumOfQuantity On Hand" field from showing up.

Any ideas?
Thanks a lot.