Multi-field query calculations
in [Queries]
|
From: vandersen70 on 10 Apr 2010 19:11 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!
From: PieterLinden via AccessMonster.com on 10 Apr 2010 19:28 vandersen70(a)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
From: vandersen70 on 10 Apr 2010 20:05 On Apr 10, 6:28 pm, "PieterLinden via AccessMonster.com" <u49887(a)uwe> wrote: > vanderse...(a)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 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?
From: John W. Vinson on 10 Apr 2010 20:31 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 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]
From: vandersen70 on 10 Apr 2010 20:38
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"))); |