Query with Three Arguments (1 Viewer)

Alhakeem1977

Registered User.
Local time
Today, 07:37
Joined
Jun 24, 2017
Messages
308
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:

Code:
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

I’m here to help
Staff member
Local time
Yesterday, 21:37
Joined
Oct 29, 2018
Messages
18,962
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

Forum Troll
Local time
Yesterday, 23:37
Joined
Oct 6, 2019
Messages
2,884
Code:
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

Registered User.
Local time
Today, 07:37
Joined
Jun 24, 2017
Messages
308
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

Registered User.
Local time
Today, 07:37
Joined
Jun 24, 2017
Messages
308
Code:
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

I’m here to help
Staff member
Local time
Yesterday, 21:37
Joined
Oct 29, 2018
Messages
18,962
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!
 

Users who are viewing this thread

Top Bottom