What is wrong with this equation?
in [Queries]
|
From: Phil Smith on 24 Mar 2010 20:01 What is wrong with this? Results:[item]![old_sku] In ("11111654","11111656","11111658","11111659","11111660","11111803","11111881","11111882 ") When I put that as a field in a select query and run it, I get either a 0 or a -1. This is fine. When I use that field as the Column Heading in a crosstab query, I get each individual item.old_sku. Why don't I get the same 0 and -1? I tried to fix it by changing the field to this: Results:iif( [item]![old_sku] In ("11111654","11111656","11111658","11111659","11111660","11111803","11111881","11111882 ") = 0, "no","yes") But I get "The operator you entered rrequires parenthesis." What the heck? Phil Access 2007
From: Duane Hookom on 24 Mar 2010 22:02 Crosstabs are picky about the syntax. They usually puke at the notion of subqueries so this might be related. I would create a small table and model this in data. Enter the SKUs in a table and outer join it in your crosstab so you don't have to use the list. -- Duane Hookom MS Access MVP "Phil Smith" <phil(a)nhs-inc.com> wrote in message news:OhOXh56yKHA.5040(a)TK2MSFTNGP02.phx.gbl... > What is wrong with this? > > Results:[item]![old_sku] In > ("11111654","11111656","11111658","11111659","11111660","11111803","11111881","11111882 > ") > > When I put that as a field in a select query and run it, I get either a 0 > or a -1. This is fine. When I use that field as the Column Heading in a > crosstab query, I get each individual item.old_sku. Why don't I get the > same 0 and -1? > > I tried to fix it by changing the field to this: > > Results:iif( [item]![old_sku] In > ("11111654","11111656","11111658","11111659","11111660","11111803","11111881","11111882 > ") = 0, "no","yes") > > But I get "The operator you entered rrequires parenthesis." > > What the heck? > > Phil > Access 2007 >
From: Daryl S on 26 Mar 2010 10:23 Phil - Remove the '= 0' - like this: Results:iif( [item]![old_sku] In ("11111654","11111656","11111658","11111659","11111660","11111803","11111881","11111882 "), "no","yes") -- Daryl S "Phil Smith" wrote: > What is wrong with this? > > Results:[item]![old_sku] In > ("11111654","11111656","11111658","11111659","11111660","11111803","11111881","11111882 > ") > > When I put that as a field in a select query and run it, I get either a > 0 or a -1. This is fine. When I use that field as the Column Heading > in a crosstab query, I get each individual item.old_sku. Why don't I > get the same 0 and -1? > > I tried to fix it by changing the field to this: > > Results:iif( [item]![old_sku] In > ("11111654","11111656","11111658","11111659","11111660","11111803","11111881","11111882 > ") = 0, "no","yes") > > But I get "The operator you entered rrequires parenthesis." > > What the heck? > > Phil > Access 2007 > > . >
|
Pages: 1 Prev: Multi List combo as criteria Next: Nested IIf and IsNull |