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!

vandersen70@yahoo.com wrote:
>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)

-- 
Message posted via http://www.accessmonster.com

On Apr 10, 6:28=A0pm, "PieterLinden via AccessMonster.com" <u49887@uwe>
wrote:
> vanderse...@yahoo.com wrote:
> >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] =3D or < [ReorderPoint]),
> >[ReorderAmount]
>
> >Thanks!
>
> The syntax is
> IIF([PhysInventory]<=3D[ReorderPoint], TRUE_VALUE, FALSE_VALUE)
>
> --
> Message posted viahttp://www.accessmonster.com- Hide quoted text -
>
> - 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?

On Sat, 10 Apr 2010 17:05:30 -0700 (PDT), vandersen70@yahoo.com wrote:

>> The syntax is
>> IIF([PhysInventory]<=[ReorderPoint], TRUE_VALUE, FALSE_VALUE)
>>
>> --
>> Message posted viahttp://www.accessmonster.com- Hide quoted text -
>>
>> - 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?
-- 

             John W. Vinson [MVP]

On Apr 10, 7:31=A0pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Sat, 10 Apr 2010 17:05:30 -0700 (PDT), vanderse...@yahoo.com wrote:
> >> The syntax is
> >> IIF([PhysInventory]<=3D[ReorderPoint], TRUE_VALUE, FALSE_VALUE)
>
> >> --
> >> Message posted viahttp://www.accessmonster.com-Hide quoted text -
>
> >> - 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 p=
ost
> the SQL view of the query?
> --
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0John W. Vinson [MVP]

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)=3DIIf([PhysInventory]<=3D[ReorderPoint],"TRUE_V=
ALUE","FALSE_VALUE")));

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

-- 
Duane Hookom
MS Access MVP


<vandersen70@yahoo.com> wrote in message 
news:2694454d-7841-487b-be6b-97771e2f6701@k33g2000yqc.googlegroups.com...
> On Apr 10, 7:31 pm, John W. Vinson
> <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
>> On Sat, 10 Apr 2010 17:05:30 -0700 (PDT), vanderse...@yahoo.com wrote:
>> >> The syntax is
>> >> IIF([PhysInventory]<=[ReorderPoint], TRUE_VALUE, FALSE_VALUE)
>>
>> >> --
>> >> Message posted viahttp://www.accessmonster.com-Hide quoted text -
>>
>> >> - 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?
>> --
>>
>>              John W. Vinson [MVP]
>
> 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"))); 

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

Duane Hookom wrote:
> You are comparing ReorderAmount with either "TRUE_VALUE" or 
> "FALSE_VALUE" which are both strings.
> 

On Sat, 10 Apr 2010 17:38:08 -0700 (PDT), vandersen70@yahoo.com wrote:

>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 [MVP]

On Sat, 10 Apr 2010 17:05:30 -0700 (PDT), vandersen70@yahoo.com wrote:

>> The syntax is
>> IIF([PhysInventory]<=[ReorderPoint], TRUE_VALUE, FALSE_VALUE)
>>
>> --
>> Message posted viahttp://www.accessmonster.com- Hide quoted text -
>>
>> - 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 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.
-- 

             John W. Vinson [MVP]

On Apr 11, 8:30=A0am, John Spencer <spen...@chpdm.edu> wrote:
> 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]<=3D[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 whe=
re
> 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]<=3D[ReorderPoint]
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
>
>
> Duane Hookom wrote:
> > 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.