Query with Three Arguments

[Alhakeem1977]

Hi All,

I would like to return the account type if three scenarios are met:


1. If the [CustTypeID] = 1 and [CCNo] = "N/A" the result should be: Account File.

2. If the [CustTypeID] = 2 and [CCNo] = "N/A" the result should be: Facility File.

3. If the [CustTypeID] = 2 and [CCNo] <> "N/A" the result should be: Credit Card File.

As the below returns -1 in the Type field:

Type: IIf([CustTypeID]=1 And [CCNo]="N/A","Account File","Facility File") Or IIf([CustTypeID]=2 Or [CCNo]="N/A","Credit Card","Facility File")

How can I achieve this in my query?

Thanks in advance!

 

[theDBguy]

Hi. First of all, "Type" may be a reserved word. Recommend maybe using AccountType instead. I think you want to "nest" your IIf() statements. For example:


IIf(FirstCondition=True,FirstResult,IIf(SecondCondition=True,SecondResult,IIf(ThirdCondition=True,ThirdResult,DefaultResult)))


Hope it helps...

 

[vba_php]

IIf([CustTypeID]=1 And [CCNo]="N/A","Account File",

iif([CustTypeID]=2 and [CCNo]="N/A", "Facility File",

iif([CustTypeID] = 2 and [CCNo] <> "N/A", "Credit Card File", "N/A")))

 

[Alhakeem1977]

Hi. First of all, "Type" may be a reserved word. Recommend maybe using AccountType instead. I think you want to "nest" your IIf() statements. For example:


IIf(FirstCondition=True,FirstResult,IIf(SecondCondition=True,SecondResult,IIf(ThirdCondition=True,ThirdResult,DefaultResult)))


Hope it helps...

Hello theDBGuy, thanks for your prompt response.

It's working fine thanks a lot.

Just to for my info, is nested means only to use the true condition in the IIF statement?

Sent from my HUAWEI NXT-L29 using Tapatalk

 

[Alhakeem1977]

IIf([CustTypeID]=1 And [CCNo]="N/A","Account File",

iif([CustTypeID]=2 and [CCNo]="N/A", "Facility File",

iif([CustTypeID] = 2 and [CCNo] <> "N/A", "Credit Card File", "N/A")))

Thanks a lot for your response.

It's working as I expected.

What a nice forum!

Sent from my HUAWEI NXT-L29 using Tapatalk

 

[theDBguy]

Hello theDBGuy, thanks for your prompt response.

It's working fine thanks a lot.

Just to for my info, is nested means only to use the true condition in the IIF statement?

Sent from my HUAWEI NXT-L29 using Tapatalk

Hi. Glad to hear you got it to work. Nested means embedding a function within another. Cheers!