Conditional inner join statement (VBA/SQL) to generate multiple values

Question

I'm quite new to VBA/SQL and I'm trying to execute a conditional inner join.

I have two tables with a column in common ("CRM" and "CodeCRM") and I would like to obtain an email adress from table2 ("Desks") when something is triggered (CodeBlocage = 101) in table1 ("Flux") in order to add it to an automatic email.

Dim StrDestinataire As String
Select Case Strtable
   Case "Flux", "GAFIJOUR"

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim Y As String
    Dim sSql As String

    Set cn = CurrentProject.Connection


    sSql = "Select AddMailCRM from Desks Inner Join Flux on Desks.CODECRM = Flux.CRM WHERE Flux.CODEBLOCAGE = '101'"

    Set rs = cn.Execute(sSql)

    If Not rs.BOF And Not rs.EOF Then
        Y = rs.Fields("AddMailCRM").Value
    End If


    StrDestinataire =  Y

    cn.Close

Everything works great except that it should be returning more than one value for the email adress. Any leads?

Thank you

Why not just add a where clause? Select email from table2 Inner Join table1 on table2.Crm = table1.Crm WHERE table1.Code = 1 ? — Bridge
– Bridge, Commented Mar 30, 2017 at 12:46
Now my problem seems to be on the "Select": Select Case Strtable Case "Flux" Select email from table2 Inner Join table1 on table2.Crm = table1.Crm WHERE table1.Code = 1 End Select — Etienne
– Etienne, Commented Mar 30, 2017 at 13:10
@Etienne can you edit your original question to reflect this please? It's unclear what you're asking now — Preston
– Preston, Commented Mar 30, 2017 at 13:42

Dick Kusleika · Accepted Answer · 2017-03-30 19:30:42Z

1

There are three keywords that may cause confusion:

SELECT in sql

SELECT determines the columns in the resulting recordset. If your sql statement is SELECT Name, Number FROM Employees, the SELECT part tells you that the resulting recordset will have two columns named Name and Number.

Select Case in VBA

Select Case is a programming construct for conditionals. You'd use it when you don't want to use a bunch of If..ElseIf..Else statements, but anything you can do with If you can do with Select Case.

Select Case A
    Case "Flux"
        Execute these VBA statements when the variable A = Flux
    Case "Capacitor"
        Execute these statements when A = Capacitor
    Case Else
        Execute these statements when A is neither Flux nor Capacitor
End Select

CASE in sql

The CASE keyword in sql is like Select Case in VBA, but it's used in the field list of a SELECT sql statement (for one).

SELECT Name, CASE WHEN Number = 1 THEN 'One' ELSE 'Two' END MyNum FROM Employees

If you execute this recordset, you get two columns (Name, MyNum). The MyNum field will contains the text One when Number is 1 for that record and the text Two if Number is anything but 1.

Recordsets

You have both Excel and Access tags, so I'm going to assume you're using ADO in either one of those. Your statement

Y = Select email from table2 Inner Join table1 on table2.Crm = table1.Crm WHERE table1.Code = 1

Doesn't do anything - it wouldn't compile. Let's assume you want a variable, Y, to contain the email that would be returned if you executed that sql statement.

Sub GetEmail()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim Y As String
    Dim sSql As String

    Set cn = New ADODB.Connection
    cn.Open "MyConnectionStringGoesHere"

    sSql = "Select email from table2 Inner Join table1 on table2.Crm = table1.Crm WHERE table1.Code = 1"

    Set rs = cn.Execute(sSql)

    If Not rs.BOF And Not rs.EOF Then
        Y = rs.Fields("email").Value
    End If

End Sub

In this case I have to create a recordset and execute that recordset for a certain connection. Presumably the join and the WHERE clause ensures that it will only return one record. But if it returns more, this example will only use the email from the first record.

Before I grab the Value of the email field, I make sure that the recordset returned at least one record. If it's at the beginning of the file (BOF) and at the end of the file (EOF) at the same time, that means there are no records in the recordset.

answered Mar 30, 2017 at 19:30

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Etienne Over a year ago

Dick thank you so much for taking the time. I just read a couple of posts about recordsets and I'm getting there. However, what is my connection string there? If you could provide me an example that would be great.

Dick Kusleika Over a year ago

The way I get connection strings: In Excel - Data - Get External Data - and create an external data table point at the database you want. Then go to the Immediate Window (Alt+F11, Ctrl+G) and type ?activecell.ListObject.QueryTable.Connection and you'll get something like this OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True... except a bit longer. The first part is OLEDB or ODBC - just remove that part and you have a connection string.

Etienne Over a year ago

Thank you Dick! You mentionned "Presumably the join and the WHERE clause ensures that it will only return one record. But if it returns more, this example will only use the email from the first record.". How could I return multiple email adresses?

Dick Kusleika Over a year ago

Instead of rs.Fields("email").Value you can use rs.GetString which will return a string of all the emails from all the records.

Etienne Over a year ago

Y = rs.GetString("AddMailCRM").Value returns the error "invalid qualifier"

|

Etienne · Accepted Answer · 2017-04-03 14:28:08Z

0

Solved.

Dim StrDestinataire As String
Select Case Strtable
   Case "Flux", "GAFIJOUR"

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim Y As String
    Dim sSql As String

    Set cn = CurrentProject.Connection


    sSql = "Select AddMailCRM from Desks Inner Join Flux on Desks.CODECRM = Flux.CRM WHERE Flux.CODEBLOCAGE = '101'"

    Set rs = cn.Execute(sSql)

    If Not rs.BOF And Not rs.EOF Then
        Y = rs.Fields("AddMailCRM").Value
    End If


    StrDestinataire =  Y

    cn.Close

edited Apr 3, 2017 at 14:28

answered Mar 31, 2017 at 13:15

Etienne

777 bronze badges

Collectives™ on Stack Overflow

Conditional inner join statement (VBA/SQL) to generate multiple values

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related