Conditional inner join statement (VBA / SQL) for generating multiple values

Question

I am new to VBA / SQL and I am trying to execute a conditional inner join.

I have two tables with a common column ("CRM" and "CodeCRM"), and I would like to get the email address from table2 ("Tables") when something starts (CodeBlocage = 101) in table 1 ("Flux ") to add it to automatic writing.

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 fine, except that it should return more than one value for the email address. Any news?

thank

Answer 1

, :

SELECT sql

SELECT . sql SELECT Name, Number FROM Employees, SELECT , Name Number.

VBA

Select Case - . , If..ElseIf..Else, , , 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 sql

CASE sql Select Case VBA, SELECT sql ( ).

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

, (Name, MyNum). MyNum One, Number - 1 , Two, Number - , 1.

Excel Access, , ADO .

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

- . , , Y , , sql.

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

. , join WHERE , . , .

, , . (BOF) (EOF) , , .

Answer 2

.

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