Multi-field query calculations
in [Queries]
|
From: Duane Hookom on 11 Apr 2010 00:59 You are comparing ReorderAmount with either "TRUE_VALUE" or "FALSE_VALUE" which are both strings. -- Duane Hookom MS Access MVP <vandersen70(a)yahoo.com> wrote in message news:2694454d-7841-487b-be6b-97771e2f6701(a)k33g2000yqc.googlegroups.com... > On Apr 10, 7:31 pm, John W. Vinson > <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: >> On Sat, 10 Apr 2010 17:05:30 -0700 (PDT), vanderse...(a)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")));
From: John Spencer on 11 Apr 2010 09:30 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. >
From: John W. Vinson on 11 Apr 2010 19:42 On Sat, 10 Apr 2010 17:38:08 -0700 (PDT), vandersen70(a)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]
From: John W. Vinson on 11 Apr 2010 20:44 On Sat, 10 Apr 2010 17:05:30 -0700 (PDT), vandersen70(a)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]
From: vandersen70 on 13 Apr 2010 14:11
On Apr 11, 8:30 am, John Spencer <spen...(a)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]<=[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.- 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. |