Select Case in VBA?

[Webtechie]

Hello,

I need to create a select case in VBA and I'm not sure how.

The table is myTable

ID NAME PAYMENT SAVINGS CHECKING CREDITCARD
1 John Smith DB x

If savings is not null, then place a "DB" in payment. If checking is not
null, then place a "DB" in payment. If CreditCard is not null, then place
"CD" in payment.

I would think this would work:

DIM strSQL as string

strSQL = "Select ID, NAME, Payment = " _
& "case " _
& "when len(savings) > 0 then """DB""" _
& "when len(checking) > 0 then """DB""" _
& "when len(creditcard) > 0 then """CD"""
& " from myTemp"

docmd.runSQL strSQL

I'm seeing in the forums that the above query won't work. How do you write
something like this in ACCESS SQL/VBA?

Thanks.

Tony

 

[John Spencer]

Use a nested IIF statement

SELECT ID, [Name]
, IIF(Savings is not null,"DB"
, IIF(Checking is not null,"DB"
, IIF(CreditCard is not null, "CD",Null))) as Payment
FROM MyTemp

Or use the VBA Switch function

SELECT ID, [Name]
, SWITCH(Savings is not null,"DB"
,Checking is not null,"DB"
,CreditCard is not null,"CD"
,True,Null) as Payment
FROM MyTemp



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Webtechie]

Thanks John.

I used the nested IIF and it worked!

Tony

Use a nested IIF statement

SELECT ID, [Name]
, IIF(Savings is not null,"DB"
, IIF(Checking is not null,"DB"
, IIF(CreditCard is not null, "CD",Null))) as Payment
FROM MyTemp

Or use the VBA Switch function

SELECT ID, [Name]
, SWITCH(Savings is not null,"DB"
,Checking is not null,"DB"
,CreditCard is not null,"CD"
,True,Null) as Payment
FROM MyTemp



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello,

I need to create a select case in VBA and I'm not sure how.

The table is myTable

ID NAME PAYMENT SAVINGS CHECKING CREDITCARD
1 John Smith DB x

If savings is not null, then place a "DB" in payment. If checking is not
null, then place a "DB" in payment. If CreditCard is not null, then place
"CD" in payment.

I would think this would work:

DIM strSQL as string

strSQL = "Select ID, NAME, Payment = " _
& "case " _
& "when len(savings) > 0 then """DB""" _
& "when len(checking) > 0 then """DB""" _
& "when len(creditcard) > 0 then """CD"""
& " from myTemp"

docmd.runSQL strSQL

I'm seeing in the forums that the above query won't work. How do you write
something like this in ACCESS SQL/VBA?

Thanks.

Tony