"Build" expression in Query

[GoBrowns!]

I need to build an expression in my query that says this:

If SupplyPoint = "LaGrange" and Status = "Y6", "SD63"
If SupplyPoint = "Mentor" and Status = "Y6", "3611"
If SupplyPoint = "Traded Goods" and Status = "Y6", "3601".

I would like this to be all in one expression, if possible.

Here was my attempt:

Expr1: IIf(([Supply_Point]="LaGrange" &
[Status]="Y6","SD63"),([Supply_Point]="Mentor" & [Status]="Y6","3611"),"3601")

Any ideas?

Thanks!

 

[Douglas J. Steele]

What value do you want if the condition's not met? The following assumes
Null...

Expr1: IIf([Status] = "Y6", Switch([Supply_Point]="LaGrange", "SD63",
[Supply_Point]="Mentor", "3611", [Supply_Point]="Traded Goods","3601"),
Null)

 

[ryguy7272]

I hope there are no Nulls in that table.
Result: IIf(([Supply_Point]="LaGrange" And
[Status]="Y6"),"SD63",IIf(([Supply_Point]="Mentor" And
[Status]="Y6"),"3611","3601"))

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

What value do you want if the condition's not met? The following assumes
Null...

Expr1: IIf([Status] = "Y6", Switch([Supply_Point]="LaGrange", "SD63",
[Supply_Point]="Mentor", "3611", [Supply_Point]="Traded Goods","3601"),
Null)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need to build an expression in my query that says this:

If SupplyPoint = "LaGrange" and Status = "Y6", "SD63"
If SupplyPoint = "Mentor" and Status = "Y6", "3611"
If SupplyPoint = "Traded Goods" and Status = "Y6", "3601".

I would like this to be all in one expression, if possible.

Here was my attempt:

Expr1: IIf(([Supply_Point]="LaGrange" &
[Status]="Y6","SD63"),([Supply_Point]="Mentor" &
[Status]="Y6","3611"),"3601")

Any ideas?

Thanks!

 

[JimBurke via AccessMonster.com]

If I'm interpreting correctly, Doug was saying that if none of those three
conditions are met then the value would be set to Null - seems like a
logical assumption to me, since the submitter didn't say what to do if none
of the conditions are met. And if they need anything other than Null, just
replace NUll with that value. Your version sets the value to "3601" in all
cases where the first two conditions are not met.

I hope there are no Nulls in that table.
Result: IIf(([Supply_Point]="LaGrange" And
[Status]="Y6"),"SD63",IIf(([Supply_Point]="Mentor" And
[Status]="Y6"),"3611","3601"))

HTH,
Ryan---

What value do you want if the condition's not met? The following assumes
Null...

[quoted text clipped - 20 lines]

 

[BTU_needs_assistance_43]

What does the program do if that condition isn't met in the way you wrote it?

What value do you want if the condition's not met? The following assumes
Null...

Expr1: IIf([Status] = "Y6", Switch([Supply_Point]="LaGrange", "SD63",
[Supply_Point]="Mentor", "3611", [Supply_Point]="Traded Goods","3601"),
Null)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need to build an expression in my query that says this:

If SupplyPoint = "LaGrange" and Status = "Y6", "SD63"
If SupplyPoint = "Mentor" and Status = "Y6", "3611"
If SupplyPoint = "Traded Goods" and Status = "Y6", "3601".

I would like this to be all in one expression, if possible.

Here was my attempt:

Expr1: IIf(([Supply_Point]="LaGrange" &
[Status]="Y6","SD63"),([Supply_Point]="Mentor" &
[Status]="Y6","3611"),"3601")

Any ideas?

Thanks!

 

[Douglas J. Steele]

It returns Null.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

What does the program do if that condition isn't met in the way you wrote
it?

What value do you want if the condition's not met? The following assumes
Null...

Expr1: IIf([Status] = "Y6", Switch([Supply_Point]="LaGrange", "SD63",
[Supply_Point]="Mentor", "3611", [Supply_Point]="Traded Goods","3601"),
Null)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need to build an expression in my query that says this:

If SupplyPoint = "LaGrange" and Status = "Y6", "SD63"
If SupplyPoint = "Mentor" and Status = "Y6", "3611"
If SupplyPoint = "Traded Goods" and Status = "Y6", "3601".

I would like this to be all in one expression, if possible.

Here was my attempt:

Expr1: IIf(([Supply_Point]="LaGrange" &
[Status]="Y6","SD63"),([Supply_Point]="Mentor" &
[Status]="Y6","3611"),"3601")

Any ideas?

Thanks!

 

[JimBurke via AccessMonster.com]

The way Doug coded it, if none of the 3 conditions are met the expression is
set to Null. If you want it set to anything else, just replace 'Null" with
whatever value you want in place of it.

What does the program do if that condition isn't met in the way you wrote it?

What value do you want if the condition's not met? The following assumes
Null...

[quoted text clipped - 20 lines]