Select Case in VBA?

W

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
 
J

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
 
W

Webtechie

Thanks John.

I used the nested IIF and it worked!

Tony

John Spencer said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top