"Build" expression in Query

  • Thread starter Thread starter GoBrowns!
  • Start date Start date
G

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!
 
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)
 
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''.


Douglas J. Steele said:
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!)


GoBrowns! said:
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!
Click to expand...
Click to expand...
 
What does the program do if that condition isn't met in the way you wrote it?

Douglas J. Steele said:
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!)


GoBrowns! said:
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!
Click to expand...
Click to expand...
 
It returns Null.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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

Douglas J. Steele said:
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!)


GoBrowns! said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top