Assign value based on value field in query
in [Help]
|
From: Thomas Andersson on 13 Aug 2010 18:20 I'm making a query where I want to create a new column with a value based on another columns content The source will contain either of 4 3-letter acronyms (RTB, MIA, RES, KIA). the new coumn should check this and assign a value of 1 for RTB and 0,5 for all others. How would I do this?
From: Thomas Andersson on 13 Aug 2010 18:54 Thomas Andersson wrote: > I'm making a query where I want to create a new column with a value > based on another columns content > The source will contain either of 4 3-letter acronyms (RTB, MIA, RES, > KIA). the new coumn should check this and assign a value of 1 for RTB > and 0,5 for all others. > How would I do this? I thought bthis would do it but keep getting errors IIf([Sortie].[Result] = "RTB", "1", "0,5")
From: Douglas J. Steele on 13 Aug 2010 18:56 What error? -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele/AccessIndex.html Co-author: "Access 2010 Solutions", published by Wiley (no private e-mails, please) "Thomas Andersson" <thomas(a)tifozi.net> wrote in message news:8cm0okFu8hU1(a)mid.individual.net... > Thomas Andersson wrote: > >> I'm making a query where I want to create a new column with a value >> based on another columns content >> The source will contain either of 4 3-letter acronyms (RTB, MIA, RES, >> KIA). the new coumn should check this and assign a value of 1 for RTB >> and 0,5 for all others. >> How would I do this? > > I thought bthis would do it but keep getting errors > IIf([Sortie].[Result] = "RTB", "1", "0,5") > >
From: Salad on 13 Aug 2010 18:59 Thomas Andersson wrote: > I'm making a query where I want to create a new column with a value based on > another columns content > The source will contain either of 4 3-letter acronyms (RTB, MIA, RES, KIA). > the new coumn should check this and assign a value of 1 for RTB and 0,5 for > all others. > How would I do this? > > In the query builder enter the column name, a colon, then expression. Ex: [A] is Acronym NumVal : IIF([A]="RTB",1,IIF([A]="MIA",0,5)) Whats the diff between 0 and 5 values?
From: Thomas Andersson on 13 Aug 2010 19:06
Douglas J. Steele wrote: Invalid Syntax, missing operand, non quoted text or invalid char/comma. > What error? >>> I'm making a query where I want to create a new column with a value >>> based on another columns content >>> The source will contain either of 4 3-letter acronyms (RTB, MIA, >>> RES, KIA). the new coumn should check this and assign a value of 1 >>> for RTB and 0,5 for all others. >>> How would I do this? >> >> I thought bthis would do it but keep getting errors >> IIf([Sortie].[Result] = "RTB", "1", "0,5") |