Multi-field query calculations

[vandersen70]

Hi all,

Admittedly, I have not programmed anything in years and Access 2007
seems to have some differences, but I am embarrased to say I can
figure out how to create a multi-field expression in a query that will
provide the following results in a report:

Here is what I want to achieve:

Reorder Alert: If the Physical Inventory is equal to or less than the
Reorder Point then show the Reorder Amount in the report.

This is what I did based on what I read, but it doesn't work. What am
I doing wrong?

ReorderAlert: IIf ([PhysInventory] = or < [ReorderPoint]),
[ReorderAmount]

Thanks!

 

[PieterLinden via AccessMonster.com]

Hi all,

Admittedly, I have not programmed anything in years and Access 2007
seems to have some differences, but I am embarrased to say I can
figure out how to create a multi-field expression in a query that will
provide the following results in a report:

Here is what I want to achieve:

Reorder Alert: If the Physical Inventory is equal to or less than the
Reorder Point then show the Reorder Amount in the report.

This is what I did based on what I read, but it doesn't work. What am
I doing wrong?

ReorderAlert: IIf ([PhysInventory] = or < [ReorderPoint]),
[ReorderAmount]

Thanks!

The syntax is
IIF([PhysInventory]<=[ReorderPoint], TRUE_VALUE, FALSE_VALUE)

 

[vandersen70]

Hi all,

Admittedly, I have not programmed anything in years and Access 2007
seems to have some differences, but I am embarrased to say I can
figure out how to create a multi-field expression in a query that will
provide the following results in a report:

Here is what I want to achieve:

Reorder Alert: If the Physical Inventory is equal to or less than the
Reorder Point then show the Reorder Amount in the report.

This is what I did based on what I read, but it doesn't work. What am
I doing wrong?

ReorderAlert: IIf ([PhysInventory] = or < [ReorderPoint]),
[ReorderAmount]

Thanks!

The syntax is
IIF([PhysInventory]<=[ReorderPoint], TRUE_VALUE, FALSE_VALUE)

Hi Pieter,

Thanks, put in the expression just as you noted but it is not working.
Get "Data type mismatch in criteria expression" error. Any ideas?

 

[John W. Vinson]

The syntax is
IIF([PhysInventory]<=[ReorderPoint], TRUE_VALUE, FALSE_VALUE)

Hi Pieter,

Thanks, put in the expression just as you noted but it is not working.
Get "Data type mismatch in criteria expression" error. Any ideas?

What are the datatypes of PhysInventory and ReorderPoint?

This may (probably is) a problem unrelated to the IIF syntax; could you post
the SQL view of the query?

 

[vandersen70]

The syntax is
IIF([PhysInventory]<=[ReorderPoint], TRUE_VALUE, FALSE_VALUE)

Hi Pieter,

Thanks, put in the expression just as you noted but it is not working.
Get "Data type mismatch in criteria expression" error. Any ideas?

What are the datatypes of PhysInventory and ReorderPoint?

This may (probably is) a problem unrelated to the IIF syntax; could you post
the SQL view of the query?

Hi John,

I checked the data types in the table thinking I may have left some
imported data as text, but these fileds are all integers. Here is the
SQL view:

SELECT Inventory.ID, Inventory.Part_No, Inventory.Description,
Inventory.Type, Inventory.Class, Inventory.Sales_Code,
Inventory.Purch_Price, Inventory.PhysInventory,
Inventory.ReorderPoint, Inventory.ReorderAmount
FROM Inventory
WHERE
(((Inventory.ReorderAmount)=IIf([PhysInventory]<=[ReorderPoint],"TRUE_VALUE","FALSE_VALUE")));

 

[Duane Hookom]

You are comparing ReorderAmount with either "TRUE_VALUE" or "FALSE_VALUE"
which are both strings.

--
Duane Hookom
MS Access MVP

The syntax is
IIF([PhysInventory]<=[ReorderPoint], TRUE_VALUE, FALSE_VALUE)

- Show quoted text -

Hi Pieter,

Thanks, put in the expression just as you noted but it is not working.
Get "Data type mismatch in criteria expression" error. Any ideas?

What are the datatypes of PhysInventory and ReorderPoint?

This may (probably is) a problem unrelated to the IIF syntax; could you
post
the SQL view of the query?

Hi John,

I checked the data types in the table thinking I may have left some
imported data as text, but these fileds are all integers. Here is the
SQL view:

SELECT Inventory.ID, Inventory.Part_No, Inventory.Description,
Inventory.Type, Inventory.Class, Inventory.Sales_Code,
Inventory.Purch_Price, Inventory.PhysInventory,
Inventory.ReorderPoint, Inventory.ReorderAmount
FROM Inventory
WHERE
(((Inventory.ReorderAmount)=IIf([PhysInventory]<=[ReorderPoint],"TRUE_VALUE","FALSE_VALUE")));

 

[John Spencer]

If you want to show/hide the value of ReOrderPoint in the query then

SELECT Inventory.ID, Inventory.Part_No, Inventory.Description,
Inventory.Type, Inventory.Class, Inventory.Sales_Code
, Inventory.Purch_Price, Inventory.PhysInventory,
,IIf([PhysInventory]<=[ReorderPoint],ReorderPoint,Null) as Reorder
, Inventory.ReorderAmount
FROM Inventory

If you want to show only those records where the reorder point has been
reached then you don't need a IIF clause at all, just a change to the where
clause.
SELECT Inventory.ID, Inventory.Part_No, Inventory.Description,
Inventory.Type, Inventory.Class, Inventory.Sales_Code,
Inventory.Purch_Price, Inventory.PhysInventory,
Inventory.ReorderPoint, Inventory.ReorderAmount
FROM Inventory
WHERE [PhysInventory]<=[ReorderPoint]


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

SELECT Inventory.ID, Inventory.Part_No, Inventory.Description,
Inventory.Type, Inventory.Class, Inventory.Sales_Code,
Inventory.Purch_Price, Inventory.PhysInventory,
Inventory.ReorderPoint, Inventory.ReorderAmount
FROM Inventory
WHERE
(((Inventory.ReorderAmount)=IIf([PhysInventory]<=[ReorderPoint],"TRUE_VALUE","FALSE_VALUE")));

You're comparing a number field ReorderAmount with a text string "True_Value"
or "False_Value". These text strings are not numbers so you'll get this error.

Could you explain what you're expecting? what you're trying to accomplish with
the query?

 

[John W. Vinson]

The syntax is
IIF([PhysInventory]<=[ReorderPoint], TRUE_VALUE, FALSE_VALUE)

Hi Pieter,

Thanks, put in the expression just as you noted but it is not working.
Get "Data type mismatch in criteria expression" error. Any ideas?

What Pieter was suggesting is that you put the value that *YOU* want to use as
the comparison in place of TRUE_VALUE and FALSE_VALUE. It's not clear from
your original post what you want this IIF to do.

 

[vandersen70]

If you want to show/hide the value of ReOrderPoint in the query then

SELECT Inventory.ID, Inventory.Part_No, Inventory.Description,
Inventory.Type, Inventory.Class, Inventory.Sales_Code
, Inventory.Purch_Price, Inventory.PhysInventory,
,IIf([PhysInventory]<=[ReorderPoint],ReorderPoint,Null) as Reorder
, Inventory.ReorderAmount
FROM Inventory

If you want to show only those records where the reorder point has been
reached then you don't need a IIF clause at all, just a change to the where
clause.
SELECT Inventory.ID, Inventory.Part_No, Inventory.Description,
Inventory.Type, Inventory.Class, Inventory.Sales_Code,
Inventory.Purch_Price, Inventory.PhysInventory,
Inventory.ReorderPoint, Inventory.ReorderAmount
FROM Inventory
WHERE [PhysInventory]<=[ReorderPoint]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

You are comparing ReorderAmount with either "TRUE_VALUE" or
"FALSE_VALUE" which are both strings.- Hide quoted text -

- Show quoted text -

Thanks John Spencer! This did the trick.
Sorry I wasn't more explicit earlier. Like I said, yikes, it's been a
while. Getting my feet wet again.

Appreciate everyone's help.