Needing help to writing a basic iif formula

[masond]

Hey All

I am hoping you can help

I am new to Access so my apologises for lack of terminology





I have created the following formula to calculate the Channel Column =Channel: IIf([Sub-Source] Is Null,[How did you hear of us],[Sub-Source])


I need to create an iff formula to run off of the channel column to say

If [Channel] Is blank and [Call ID] >1 then “PPC”, otherwise [Channel]

[rpeare]

iif(isnull([channel]) and [call id] > 1, "PPC", [Channel])


what happens if channel is null and [call id] is not greater than 1 though? that's the hole in your logic.

[masond]

Hi rpeare

[Call id] can only be null or >1

[rpeare]

yes but you have 3 possible cases, and you've only accounted for 2

1. CHANNEL is NOT blank, in which case you want CHANNEL to show
2. CHANNEL *is* blank
a. If CALL ID > 1 you want PPC to show
b. If CALL ID is anything *except* a value greater than 1 what happens?

in the case of b. if you want CHANNEL to be what is displayed your formula is ok. I personally prefer to plug logical holes like this:

iif(isnull([channel]), (iif([call id] > 1, "PPC", [Channel])), [Channel])

[masond]

HI Rpeare

I see what you mean,

Okay if there is any value (which is only going to be numbers) in the [Call id] column then it needs to be PPC. if there is no [Call id] then i need [Channel] to be displayed

[rpeare]

then either formula will work, I just prefer to plug all holes in case it becomes necessary in the future to do something with different conditions it then becomes fairly easy to accommodate those changes.

[masond]

HI Rpeare

Thank you for your help

Do you know how i can change the formula you have provided into a VBA statement ?
i need the logic to be calculated on a table called test, and put the results into a table called test2

[rpeare]

it's a calculated value, why would you want to store it at all? It would be meaningless to add it as a value to a secondary table because your data in the first table could change and it would invalidate the value in your second table. Calculated values should never be stored in a table.

[masond]

Hi Rpeare

I am having it stored in the table, as i can manipulate it easier with case statements then calculating it on the fly.
The data in the first table will always remain the same, the formula you have provided is correct, and this logic will never change.

From the calculated column provided in your column

i have will produce the the following case

Sub ModifyMe()
strsql = "select Channel, " _
& "Switch(" _
& "Channel in('Internet PPC','Offer-1for3month-Jan13','Offer-Switch-Jan13'), 'PPC' , " _
& "Channel in('Qualified','At Work'), 'SE0'" _
& ") as ChannelCorrect into test2 from test"


DoCmd.RunSQL strsql
End Sub