Hi,
I'm new to VBA and have only been using it for 3 months, so please don't laugh!
I have a VBA routine in Excel, which connects to a SQL Server database, and I need to know how to incorporate an IF expression into the SQL statement. The SQL looks like this in the MS Access query I am trying to re-create:
SELECT IIf(IsNumeric([PromoSource]),"REC",IIf(IsNull([PromoSource]),"OTHER",IIf([PromoSource]="WWW","Web",[PromoSource]))) AS [Promo Source], Count(dbo_tblCustomers.CustomerID) AS CountOfCustomerID
FROM dbo_tblCustomers
WHERE (((dbo_tblCustomers.DateEntered)>=#1/1/2006#))
GROUP BY IIf(IsNumeric([PromoSource]),"REC",IIf(IsNull([PromoSource]),"OTHER",IIf([PromoSource]="WWW","Web",[PromoSource])));
The field PromoSource is a combination of numerical and text values and it's this I want to evaluate in the expression. I also need to GROUP BY this expression in the query result. Any ideas on how I can incorporate this expression into my VBA routine Below is the relevant extract from my routine as it stands:
Sub PromoSourceAnalysis()
'Declare Variables
Dim RS1 As ADODB.Recordset
Dim Conn1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Dim ReportDate As Date
ReportDate = #1/1/2006#
Conn1.ConnectionString = "driver={SQL Server};server=le08;database=amo_reporting"
Conn1.Open
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "SELECT tblCustomers.PromoSource, tblCustomers.CustomerID " & _
"FROM tblCustomers " & _
"WHERE tblCustomers.DateEntered >= '" & Format(ReportDate, "yyyy-mm-dd") & "'"
Cmd1.CommandTimeout = 100
Cmd1.Parameters.Refresh
Set RS1 = Cmd1.Execute()
I hope there is enough info here for anyone to be able to help.
Cheers!
Keith
SQL within a VBA Module
Norbert Thek
yeah the second example is a bit of a bracket fest... wasn't me!!
Here's some info on the SELECT CASE statement...
http://msdn.microsoft.com/library/default.asp url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp
Spyder_Snyper
ititrx
Hi,
Thanks for the reply!
I've tried that and I get a run-time error "Incorrect Syntax near the keyword THEN". Below is what it looks like in my routine:
Cmd1.CommandText = "SELECT CASE " & _
"WHEN ISNUMERIC(tblCustomers.PromoSource) THEN 'REC' " & _
"WHEN ISNULL(tblCustomers.PromoSource) THEN 'Other' " & _
"ELSE tblCustomers.PromoSource " & _
"END As 'PromotionCode', tblCustomers.CustomerID " & _
"FROM tblCustomers " & _
"WHERE tblCustomers.DateEntered >= '" & Format(ReportDate, "yyyy-mm-dd") & "'"
I was going to try your second example, but looking at it, I'm unsure how the parenthesis are working on each of the WHEN statements (e.g. the first is closed off with two brackets, the second with one etc). I wasn't quite sure of the structure. Otherwise that could work.
I'll continue to plug away at it. Cheers for your help!
Keith
Learning VB
Hi,
I've managed to get it to work! See code below:
Conn1.ConnectionString = sConnectAmo
Conn1.Open
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "SELECT 'PromotionSource' = " & _
"CASE " & _
"WHEN ISNUMERIC(PromoSource) = 1 THEN 'REC' " & _
"WHEN PromoSource IS NULL THEN 'Other' " & _
"WHEN PromoSource = 'WWW' THEN 'Web' " & _
"ELSE PromoSource " & _
"END, " & _
"Count(CustomerID) As 'CountOfCustomers' " & _
"FROM tblCustomers " & _
"WHERE DateEntered >= '" & Format(ReportDate, "yyyy-mm-dd") & "' " & _
"GROUP BY CASE " & _
"WHEN ISNUMERIC(PromoSource) = 1 THEN 'REC' " & _
"WHEN PromoSource IS NULL THEN 'Other' " & _
"WHEN PromoSource = 'WWW' THEN 'Web' " & _
"ELSE PromoSource " & _
"END"
Cmd1.CommandTimeout = 100
Cmd1.Parameters.Refresh
Set RS1 = Cmd1.Execute()
Many thanks for your help!! I can see this forum will be an extremely valuable resource for me as I explore the world of VBA. Thanks again!
Keith
Kryor
Hello,
You could try the CASE statement instead of the IIF statement. In theory it should work although I haven't used it with ADO. If it doesn't work I'd suggest creating a stored procedure and calling that though ADO.
SELECT CASE
WHEN ISNUMERIC([PromoSource]) THEN "REC"
WHEN ISNULL([PromoSource]) THEN "OTHER"
ELSE 0
END
Here's another example:
SELECT TicketTable.TruckerName, TicketTable.UnitNumber, TicketTable.Date, CASE (TicketTable.TruckType)
(WHEN ( "s" (THEN (UnitHaulRateTable1.StandAmt))
(WHEN ("d" (THEN (UnitHaulRateTable1.DualAmt)
(WHEN ("m" (THEN (UnitHaulRateTable1.MuleAmt)))))))))
FROM TicketTable, UnitHaulRateTable1
WHERE TicketTable.UnitNumber=UnitHaulRateTable1.UnitNumber AND TicketTable.DestinationCode=UnitHaulRateTable1.DestinationCode