Multiple IIF logic in Query statement 2

[Laura453]

Hi, I need to amend the following code, which checks value of "consumer postcode" and "stock indicator". If there is no "consumer postcode" and "stock indicator" = "D" consumer post code is set to 9"." characters:

StringInfo2: IIf(([Consumer PostCode] Is Null And [Stock Indicator]="D"),String(9,"."),Left([Consumer PostCode] & String(9," "),9))

I need to amend this to include multiple IF thens... and am struggling a bit!

The revised logic needs to be:
If postcode =null AND stockID=D; post code = "......."
ELSE
IF postcode is NOT NULL and STOCK ID= D; post code = post code plus blanks to make string 9 chars
ELSE
IF postcode = null and Stock ID not=D or blank; post code = 9 blanks
ELSE
IF postcode is NOT NULL and STOCK ID not D or blank; postcode = post code plus blanks to make string 9 chars.

Hope someone can help with this.
Many thanks!
Regards, Laura.

 

[dhookom]

Consider building a small public function that accepts the PostCode and StockID returning the appropriate value.

Public Function GetStringInfo2(varPostCode as Variant, _
    varStockID as Variant) as String
    If IsNull(varPostCode) And varStockID & "" = "D" Then
        GetStringInfo2 = String(9,"."
    End If
   'other tests (Ifs)
End Function

You can then use this function in your query
StringInfo2: GetStringInfo2([Consumer PostCode],[Stock Indicator])


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Golom]

Try

StringInfo2: IIf (PostCode IS NOT NULL, Left(PostCode & String(9," "),9),
             IIf([Stock ID] = 'D', String(9,"."), String(9," ")))