条件内连接语句(VBA/SQL)生成多个值

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

我对VBA/SQL非常陌生，我正在尝试执行一个条件内连接。

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.

我有两个表，它们有一个相同的列(“CRM”和“CodeCRM”)，我想在表1(“Flux”)中触发(CodeBlocage = 101)时从表2(“desk”)获得一个电子邮件地址，以便将其添加到自动电子邮件中。

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

谢谢你！

2 个解决方案

#1

There are three keywords that may cause confusion:

有三个关键词可能引起混淆:

SELECT in sql

选择在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.

选择确定结果记录集中的列。如果sql语句是SELECT Name、Number FROM Employees，那么SELECT部分将告诉您结果的记录集将有两个列Name和Number。

Select Case in VBA

选择在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是条件句的编程构造。当你不想用一堆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 in sql

在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).

sql中的CASE关键字类似于VBA中的Select CASE，但它用于Select sql语句的字段列表(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.

如果执行此记录集，将得到两个列(Name, MyNum)。MyNum字段将包含该记录的Number为1时的text 1，如果Number不是1则是text 2。

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

你有Excel和Access标签，所以我假设你在其中一个中使用ADO。你的语句

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.

什么都不做——它不会编译。假设您想要一个变量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

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.

在这种情况下，我必须创建一个记录集并为某个连接执行该记录集。假定连接和WHERE子句确保它只返回一个记录。但是如果它返回的更多，这个例子只会使用第一个记录的电子邮件。

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.

在获取电子邮件字段的值之前，我要确保记录集至少返回一条记录。如果它在文件(BOF)的开头，同时在文件(EOF)的末尾，这意味着记录集中没有记录。

#2

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

#1