Assign value based on value field in query

#1

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?

#2

Thomas Andersson wrote:

Quote:

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")

#3

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 (AT) tifozi (DOT) net> wrote

Quote:

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")

#4

Thomas Andersson wrote:

Quote:

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?

#5

Douglas J. Steele wrote:

Invalid Syntax, missing operand, non quoted text or invalid char/comma.

Quote:

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")

#6

Salad wrote:

Quote:

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?

If they RTB (Return to Base) they get full credit for their mission,
otherwise they only get half. (the second choise is 0,5 (half), not 0 and 5
BTW). T&hat solutions is the same I'm trying except you nested yours.
Hmm, since I'm gonna use an additional query to count all the credits up
maybe I should set it to 2 and 1 instead and then just half the total in the
second query?

#7

"Thomas Andersson" <thomas (AT) tifozi (DOT) net> wrote

Quote:

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")

Try using a period as a decimal point instead of a comma:

IIf([Sortie].[Result] = "RTB", "1", "0.5")

#8

Stuart McCall wrote:

Quote:

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")

Try using a period as a decimal point instead of a comma:

IIf([Sortie].[Result] = "RTB", "1", "0.5")

Doubt it will help as I tried using 2 and 1 as values and get the same
error.
This is a headscratcher as according to all examples I've found this SHOULD
be correct.
Theer are no extra commas, all text and numbers are enclosed in quotes and
the only operand is = which is legal.. so WHAT is Access complaining about??

#9

Thomas Andersson wrote:

Quote:

Stuart McCall 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")

Try using a period as a decimal point instead of a comma:

IIf([Sortie].[Result] = "RTB", "1", "0.5")

Doubt it will help as I tried using 2 and 1 as values and get the same
error.
This is a headscratcher as according to all examples I've found this SHOULD
be correct.
Theer are no extra commas, all text and numbers are enclosed in quotes and
the only operand is = which is legal.. so WHAT is Access complaining about??

Throwing mud at wall, try [Sortie]![Result]. Then try simply [Result].

Then verify there really is a Result field and it is not misspelled.
Then try 1 and 2 without quotes. Then pull out .44 magnum and snear at
screen and say "Do you feel lucky, punk? Well do you?"

#10

So what's the SQL for the entire query?

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: "Access 2010 Solutions", published by Wiley
(no e-mails, please!)

"Thomas Andersson" <thomas (AT) tifozi (DOT) net> wrote

Quote:

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")