将SQL结果返回到Excel的VBA和SQL

Question

现在我终于有时间回顾一下我一年多前写的这段代码了。我需要帮助精简我所写的代码。我必须编写大量的IF语句才能使其正常工作，但我认为使用函数和字典可能是更有效的方法来处理这些代码。我的技能还不是最好的，所以一些想法和例子，如何设置这段代码不仅可以更有效地运行，而且还可以简化代码本身，这将是非常有用的。这段代码确实运行并给出了所需的结果。

此代码根据用户在UserForm上输入的条件从IBM /400服务器运行SQL搜索。

Dim wsCity As Range, wsState As Range, wsAgeL As Range, wsAgeU As Range, wsGender As Range, wsDOB As Range, wsAge As Range
Dim strConn As String, strSQL As String, uName As String, empName As String, lableCap As String, sqlCity As String, sqlState As String, sqlGender As String
Dim CS As New ADODB.Connection, RS As New ADODB.Recordset
Private Sub Search_Click()
    Dim wsDD As Worksheet
    Dim DOBRange As Range, AgeRange As Range
    Dim CitySQL As String, StateSQL As String, DOBSQL As String, CustSQL As String, sqlAgeLStr As String, sqlAgeUStr As String, sqlAgeBStr As String
    Dim lastRowDOB As Long, lastRowAge As Long, i As Long, lastx As Long
    Dim cell
    Dim x As Long, a As Integer, aLower As Integer, aUpper As Integer

    Set CS = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")

    Set wsCity = DE.Range("City")
    Set wsState = DE.Range("State")
    Set wsDOB = DE.Range("DOB")
    Set wsGender = DE.Range("Gender")
    Set wsAgeL = DE.Range("AgeLower")
    Set wsAgeU = DE.Range("AgeUpper")

    aLower = wsAgeL
    aUpper = wsAgeU

    sqlAgeLStr = "TIMESTAMPDIFF(256, CHAR(TIMESTAMP(CURRENT TIMESTAMP) - TIMESTAMP(DATE(DIGITS(DECIMAL(cfdob7 + 0.090000, 7, 0))), CURRENT TIME))) >=  " & aLower & "" & ""
    Debug.Print sqlAgeLStr
    sqlAgeUStr = "TIMESTAMPDIFF(256, CHAR(TIMESTAMP(CURRENT TIMESTAMP) - TIMESTAMP(DATE(DIGITS(DECIMAL(cfdob7 + 0.090000, 7, 0))), CURRENT TIME))) >=  " & aUpper & "" & ""
    Debug.Print sqlAgeUStr
    sqlAgeBStr = "TIMESTAMPDIFF(256, CHAR(TIMESTAMP(CURRENT TIMESTAMP) - TIMESTAMP(DATE(DIGITS(DECIMAL(cfdob7 + 0.090000, 7, 0))), CURRENT TIME))) BETWEEN " & aLower & " AND " & aUpper & ""
    Debug.Print sqlAgeBStr

    Application.ScreenUpdating = False

    strConn = REDACTED FOR PUBLIC VIEWING

    sqlCity = wsCity.Value
    sqlState = wsState.Value
    sqlGender = wsGender.Value

    strSQL = "SELECT " & _
                "cfna1,CFNA2,CFNA3,CFCITY,CFSTAT,LEFT(CFZIP,5) FROM CNCTTP08.JHADAT842.CFMAST CFMAST " & _
                "WHERE cfdob7 != 0 AND cfdob7 != 1800001 AND CFDEAD = 'N' AND "

    a = 0

'SEARCHES BY CITY ONLY
    If wsCity.Value <> vbNullString And wsState.Value = vbNullString And wsGender.Value = vbNullString And _
    wsAgeL = vbNullString And wsAgeU = vbNullString Then a = 1

'SEARCHES BY CITY AND STATE
    If wsCity.Value <> vbNullString And wsState.Value <> vbNullString And wsGender.Value = vbNullString And _
    wsAgeL = vbNullString And wsAgeU = vbNullString Then a = 2

'SEARCHES BY CITY AND GENDER
    If wsCity.Value <> vbNullString And wsState.Value = vbNullString And wsGender.Value <> vbNullString And _
    wsAgeL = vbNullString And wsAgeU = vbNullString Then a = 3

'SEARCHES BY CITY AND AGE LOWER
    If wsCity.Value <> vbNullString And wsState.Value = vbNullString And wsGender.Value = vbNullString And _
    wsAgeL <> vbNullString And wsAgeU = vbNullString Then a = 4

'SEARCHES BY CITY AND AGE UPPER
    If wsCity.Value <> vbNullString And wsState.Value = vbNullString And wsGender.Value = vbNullString And _
    wsAgeL = vbNullString And wsAgeU <> vbNullString Then a = 5

'SEARCHES BY CITY AND FULL AGE RANGE
    If wsCity.Value <> vbNullString And wsState.Value = vbNullString And wsGender.Value = vbNullString And _
    wsAgeL <> vbNullString And wsAgeU <> vbNullString Then a = 6

'SEARCHES BY CITY, GENDER AND FULL AGE RANGE
    If wsCity.Value <> vbNullString And wsState.Value = vbNullString And wsGender.Value <> vbNullString And _
    wsAgeL <> vbNullString And wsAgeU <> vbNullString Then a = 7

'SEARCHES BY CITY, STATE AND GENDER
    If wsCity.Value <> vbNullString And wsState.Value <> vbNullString And wsGender.Value <> vbNullString And _
    wsAgeL = vbNullString And wsAgeU = vbNullString Then a = 8

'SEARCHES BY CITY, STATE, GENDER AND LOWER AGE
    If wsCity.Value <> vbNullString And wsState.Value <> vbNullString And wsGender.Value <> vbNullString And _
    wsAgeL <> vbNullString And wsAgeU = vbNullString Then a = 9

'SEARCHES BY CITY, STATE, GENDER, UPPER AGE RANGE
    If wsCity.Value <> vbNullString And wsState.Value <> vbNullString And wsGender.Value <> vbNullString And _
    wsAgeL = vbNullString And wsAgeU <> vbNullString Then a = 10

'SEARCHES BY CITY, STATE, GENDER, FULL AGE RANGE
    If wsCity.Value <> vbNullString And wsState.Value <> vbNullString And wsGender.Value <> vbNullString And _
    wsAgeL <> vbNullString And wsAgeU <> vbNullString Then a = 11

'SEARCHES BY STATE
    If wsCity.Value = vbNullString And wsState.Value <> vbNullString And wsGender.Value = vbNullString And _
    wsAgeL = vbNullString And wsAgeU = vbNullString Then a = 12

'SEARCHES BY STATE AND GENDER
    If wsCity.Value = vbNullString And wsState.Value <> vbNullString And wsGender.Value <> vbNullString And _
    wsAgeL = vbNullString And wsAgeU = vbNullString Then a = 13

'SEARCHES BY STATE AND AGE LOWER
    If wsCity.Value = vbNullString And wsState.Value <> vbNullString And wsGender.Value = vbNullString And _
    wsAgeL <> vbNullString And wsAgeU = vbNullString Then a = 14

'SEARCHES BY STATE AND AGE UPPER
    If wsCity.Value = vbNullString And wsState.Value <> vbNullString And wsGender.Value = vbNullString And _
    wsAgeL = vbNullString And wsAgeU <> vbNullString Then a = 15

'SEARCHES BY STATE AND FULL AGE RANGE
    If wsCity.Value = vbNullString And wsState.Value <> vbNullString And wsGender.Value = vbNullString And _
    wsAgeL <> vbNullString And wsAgeU <> vbNullString Then a = 16

'SEARCHES BY STATE, GENDER AND AGE LOWER
    If wsCity.Value = vbNullString And wsState.Value <> vbNullString And wsGender.Value <> vbNullString And _
    wsAgeL <> vbNullString And wsAgeU = vbNullString Then a = 17

'SEARCHES BY STATE, GENDER AND AGE UPPER
    If wsCity.Value = vbNullString And wsState.Value <> vbNullString And wsGender.Value <> vbNullString And _
    wsAgeL = vbNullString And wsAgeU <> vbNullString Then a = 18

'SEARCHES BY STATE, GENDER AND FULL AGE RANGE
    If wsCity.Value = vbNullString And wsState.Value <> vbNullString And wsGender.Value <> vbNullString And _
    wsAgeL <> vbNullString And wsAgeU <> vbNullString Then a = 19

'SEARCHES BY GENDER
    If wsCity.Value = vbNullString And wsState.Value = vbNullString And wsGender.Value <> vbNullString And _
    wsAgeL = vbNullString And wsAgeU = vbNullString Then a = 20

'SEARCHES BY GENDER AND AGE LOWER
    If wsCity.Value = vbNullString And wsState.Value = vbNullString And wsGender.Value <> vbNullString And _
    wsAgeL <> vbNullString And wsAgeU = vbNullString Then a = 21

'SEARCHES BY GENDER AND AGE UPPER
    If wsCity.Value = vbNullString And wsState.Value = vbNullString And wsGender.Value <> vbNullString And _
    wsAgeL = vbNullString And wsAgeU <> vbNullString Then a = 22

'SEARCHES BY GENDER AND FULL AGE RANGE
    If wsCity.Value = vbNullString And wsState.Value = vbNullString And wsGender.Value <> vbNullString And _
    wsAgeL <> vbNullString And wsAgeU <> vbNullString Then a = 23

'SEARCHES BY LOWER AGE RANGE
    If wsCity.Value = vbNullString And wsState.Value = vbNullString And wsGender.Value = vbNullString And _
    wsAgeL <> vbNullString And wsAgeU = vbNullString Then a = 24

'SEARCHES BY UPPER AGE RANGE
    If wsCity.Value = vbNullString And wsState.Value = vbNullString And wsGender.Value = vbNullString And _
    wsAgeL = vbNullString And wsAgeU <> vbNullString Then a = 25

'SEARCHES BY FULL AGE RANGE
    If wsCity.Value = vbNullString And wsState.Value = vbNullString And wsGender.Value = vbNullString And _
    wsAgeL = vbNullString And wsAgeU = vbNullString Then a = 26

'SEARCHES BY CITY, STATE, FULL AGE RANGE
    If wsCity.Value <> vbNullString And wsState.Value <> vbNullString And wsGender.Value = vbNullString And _
    wsAgeL <> vbNullString And wsAgeU <> vbNullString Then a = 27


    Select Case a
        Case Is = 1 'SEARCHES BY CITY ONLY
            strSQL = strSQL & "CFCITY= '" & UCase(wsCity.Value) & "' AND " & _
                              "CFSEX != 'O'"
        Case Is = 2 'SEARCHES BY CITY AND STATE
            strSQL = strSQL & "CFSEX != 'O' AND " & _
                              "CFCITY = '" & UCase(wsCity.Value) & "' AND " & _
                              "CFSTAT = '" & UCase(wsState.Value) & "'"
        Case Is = 3 'SEARCHES BY CITY AND GENDER
            strSQL = strSQL & "CFCITY = '" & UCase(wsCity.Value) & "' AND " & _
                              "CFSEX = '" & wsGender & "'"
        Case Is = 4 'SEARCHES BY CITY AND AGE LOWER
            strSQL = strSQL & "CFCITY = '" & UCase(wsCity.Value) & "' AND " & _
                              sqlAgeLStr
        Case Is = 5 'SEARCHES BY CITY AND AGE UPPER
            strSQL = strSQL & "CFCITY = '" & UCase(wsCity.Value) & "' AND " & _
                              sqlAgeUStr
        Case Is = 6 'SEARCHES BY CITY AND FULL AGE RANGE
            strSQL = strSQL & "CFCITY = '" & UCase(wsCity.Value) & "' AND " & _
                              sqlAgeBStr
        Case Is = 7 'SEARCHES BY CITY, GENDER, AND FULL AGE RANGE
            strSQL = strSQL & "CFCITY = '" & UCase(wsCity.Value) & "' AND " & _
                              "CFSEX = '" & UCase(wsGender.Value) & "' AND " & _
                              sqlAgeBStr
        Case Is = 8 'SEARCHES BY CITY, STATE AND GENDER
            strSQL = strSQL & "CFCITY = '" & UCase(wsCity.Value) & "' AND " & _
                              "CFSTAT = '" & UCase(wsState.Value) & "' AND " & _
                              "CFSEX = '" & wsGender & "'"
        Case Is = 9 'SEARCHES BY CITY, STATE, GENDER AND LOWER AGE
            strSQL = strSQL & "CFCITY = '" & UCase(wsCity.Value) & "' AND " & _
                              "CFSTAT = '" & UCase(wsState.Value) & "' AND " & _
                              "CFSEX = '" & wsGender & "' AND " & _
                              sqlAgeLStr
        Case Is = 10 'SEARCHES BY CITY, STATE, GENDER, UPPER AGE RANGE
            strSQL = strSQL & "CFCITY = '" & UCase(wsCity.Value) & "' AND " & _
                              "CFSTAT = '" & UCase(wsState.Value) & "' AND " & _
                              "CFSEX = '" & wsGender & "' AND " & _
                              sqlAgeUStr
        Case Is = 11 'SEARCHES BY CITY, STATE, GENDER, FULL AGE RANGE
            strSQL = strSQL & "CFCITY = '" & UCase(wsCity) & "' AND " & _
                              "CFSTAT = '" & UCase(wsState) & "' AND " & _
                              "CFSEX = '" & UCase(wsGender) & "' AND " & _
                              sqlAgeBStr
        Case Is = 12 'SEARCHES BY STATE
            strSQL = strSQL & "CFSTAT= '" & UCase(wsState.Value) & "'"
        Case Is = 13 'SEARCHES BY STATE AND GENDER
            strSQL = strSQL & "CFSTAT = '" & UCase(wsState.Value) & "' AND " & _
                              "CFSEX = '" & wsGender & "'"
        Case Is = 14 'SEARCHES BY STATE AND AGE LOWER
            strSQL = strSQL & "CFSTAT = '" & UCase(wsState.Value) & "' AND " & _
                              sqlAgeLStr
        Case Is = 15 'SEARCHES BY STATE AND AGE UPPER
            strSQL = strSQL & "CFSTAT = '" & UCase(wsState.Value) & "' AND " & _
                              sqlAgeUStr
        Case Is = 16 'SEARCHES BY STATE AND FULL AGE RANGE
            strSQL = strSQL & "CFSTAT = '" & UCase(wsState.Value) & "') AND " & _
                              sqlAgeBStr
        Case Is = 17 'SEARCHES BY STATE, GENDER AND AGE LOWER
            strSQL = strSQL & "CFSTAT = '" & UCase(wsState.Value) & "' AND " & _
                              "CFSEX = '" & wsGender & "' AND " & _
                              sqlAgeLStr
        Case Is = 18 'SEARCHES BY STATE, GENDER AND AGE UPPER
            strSQL = strSQL & "CFSTAT = '" & UCase(wsState.Value) & "' AND " & _
                              "CFSEX = '" & wsGender & "' AND " & _
                              sqlAgeUStr
        Case Is = 19 'SEARCHES BY STATE, GENDER AND FULL AGE RANGE
            strSQL = strSQL & "CFSTAT = '" & UCase(wsState.Value) & "' AND " & _
                              "CFSEX = '" & wsGender & "' AND " & _
                              sqlAgeBStr
        Case Is = 20 'SEARCHES BY GENDER
            strSQL = strSQL & "CFSEX = '" & wsGender & "'"
        Case Is = 21 'SEARCHES BY GENDER AND AGE LOWER
            strSQL = strSQL & "CFSEX = '" & wsGender & "' AND " & _
                              sqlAgeLStr
        Case Is = 22 'SEARCHES BY GENDER AND AGE UPPER
            strSQL = strSQL & "CFSEX = '" & wsGender & "' AND " & _
                              sqlAgeUStr
        Case Is = 23 'SEARCHES BY GENDER AND FULL AGE RANGE
            strSQL = strSQL & "CFSEX = '" & wsGender & "' AND " & _
                              sqlAgeBStr
        Case Is = 24 'SEARCHES BY LOWER AGE RANGE
            strSQL = strSQL & "CFSEX != 'O' AND " & _
                              sqlAgeLStr
        Case Is = 25 'SEARCHES BY UPPER AGE RANGE
            strSQL = strSQL & "CFSEX != 'O' AND " & _
                              sqlAgeUStr
        Case Is = 26 'SEARCHES BY FULL AGE RANGE
            strSQL = strSQL & "CFSEX != 'O' AND " & _
                              sqlAgeBStr
        Case Is = 27 'SEARCHES BY CITY, STATE, FULL AGE RANGE
            strSQL = strSQL & "CFCITY = '" & UCase(wsCity) & "' AND " & _
                              "CFSTAT = '" & UCase(wsState.Value) & "' AND " & _
                              sqlAgeBStr
    End Select

    strSQL = strSQL & " ORDER BY cfna1 ASC"
    Debug.Print strSQL

    DataEntry.Hide

    CS.Open (strConn)
    RS.Open strSQL, CS

    MarketingList.Range("B2").CopyFromRecordset RS

    RS.Close
    CS.Close

    Set RS = Nothing
    Set CS = Nothing

    Application.ScreenUpdating = True

    MarketingList.Activate
    FormatHeaders
    SearchComplete.Show

End Sub

Private Sub AgeLower_AfterUpdate()

    Set wsAgeL = DE.Range("AgeLower")

    wsAgeL = Format(DataEntry.AgeLower, "0")

End Sub

Private Sub AgeUpper_AfterUpdate()

    Set wsAgeU = DE.Range("AgeUpper")

    wsAgeU = Format(DataEntry.AgeUpper, "0")
End Sub

Private Sub City_AfterUpdate()

    Set wsCity = DE.Range("City")

    wsCity = DataEntry.City
End Sub
Private Sub Male_Click()

    Set wsGender = DE.Range("Gender")

    Select Case DataEntry.Male
        Case Is = True
            wsGender = "M"
        Case Is = False
            wsGender = vbNullString
    End Select

End Sub
Function OrdDateToDate(OrdDate As String) As Long
    Dim TheYear As Integer
    Dim TheDay As Integer
    Dim TheDate As Long

    TheYear = CInt(Left(OrdDate, 4))
    TheDay = CInt(Right(OrdDate, 3))
    TheDate = DateSerial(TheYear, 1, TheDaDE)
    OrdDateToDate = TheDate

End Function
Private Sub Female_Click()
    Set wsGender = DE.Range("Gender")

    Select Case DataEntry.Female
        Case Is = True
            wsGender = "F"
        Case Is = False
            wsGender = vbNullString
    End Select
End Sub

Answer 1

让我们从简单的事情开始。乍一看，代码看起来很可怕，但是仔细看了看之后，它是可怕的。在大多数情况下，您需要学习一些将大大简化代码的技巧。

杂项

实际上，我认为类成员没有理由，因为这些字段中的每个都是在每个使用点设置的。这样，如果其中一个引用发生更改，则必须在每个使用点更新引用。

如果在初始化用户表单时一次设置字段就更有意义了。

rCity为范围，rState为范围，rAgeL为范围，rAgeU为范围，rGender为范围，rDOB为范围，rAge为Range私有子级UserForm_Initialize() Set rCity = DE.Range("City") Set rState = DE.Range("State") Set rDOB =DE.Range(“道布”) Set rGender =DE.Range=DE.Range(“性别”) Set rAgeL =DE.Range(“rGender”) Set =# End 20#(“”)

为什么在范围前加上ws呢？通常，ws表示Worksheet。

wsCity为Range，wsState为范围，wsAgeL为范围，wsAgeU为范围，wsGender为范围，wsDOB为范围，wsAge为范围

如果要使用CreateObject设置实例，为什么要使用New关键字？连接和记录集没有理由成为字段。它们应该是局部变量。

CS为新ADODB.Connection，RS为新ADODB.Recordset

您在控件AfterUpdate事件中设置类成员字段是什么？

私有子City_AfterUpdate() Set wsCity = DE.Range("City") wsCity = DataEntry.City End Sub

使用辅助变量来简化和澄清代码。除非您希望确保用户更改该值，否则不要在此设置您的字段。

使用Me而不是DataEntry。

私有子City_Change() DE.Range("City") = Me.City.Value End Sub

Sub Search_Click()

有点乱七八糟。首先，这个Search_Click()做的太多了。

• 设置类成员
• 建立连接
• 生成查询字符串
• 执行查询
• 转移

方法执行的任务越少，测试和修改就越容易。

通过使用If和If组合所有的Select Case语句，可以消除D35块。

如果Len(wsCity.Value) >0，Len(wsState.Value) =0，Len(wsGender.Value) =0，Len(wsCity.Value) >0，Len(wsState.Value) =0，Len(wsGender.Value) =0，Len(wsAgeL) =0，Len(wsAgeU) =0，那么按城市只搜索strSQL = strSQL & "CFCITY=‘& UCase(wsCity.Value) &“和CFSEX != 'O'”ElseIf Len(wsCity.Value)>0和Len(wsState.Value) >0，Len(wsGender.Value) =0，Len(wsAgeL) =0，Len(wsAgeU) =0，然后按城市和州strSQL = strSQL和"CFSEX != 'O‘和“& "CFCITY =’UCase(wsCity.Value) &‘和”和“&”‘& UCase(wsState.Value)和’‘ElseIf Len(wsCity.Value) >0和Len(wsState.Value) =0和Len(wsGender.Value) >0，Len(wsAgeL) =0和Len(wsAgeU) =0，然后再按城市和性别搜索strSQL = strSQL和"CFCITY =’& UCase(wsCity.Value) &“和”& "CFSEX =‘wsGender & "'“更多条款在以下情况下终止

或者，您可以通过使用If来消除Select Case True子句。

按城市选择Case True搜索Case Len(wsCity.Value) > 0，Len(wsState.Value) = 0，Len(wsGender.Value) = 0，Len(wsAgeL) = 0，Len(wsAgeU) =0 strSQL = strSQL & "CFCITY=‘& UCase(wsCity.Value) &’，CFSEX != 'O'“按城市和州情况搜索Len(wsCity.Value) > 0，Len(wsState.Value) > 0，Len(wsGender.Value) = 0，Len(wsAgeL) = 0，Len(wsAgeU) =0 strSQL = strSQL和"CFSEX != 'O‘和“& "CFCITY = '”& UCase(wsCity.Value)和“和”& "CFSTAT = '“& UCase(wsState.Value)和”按城市和性别分列的对物搜索“Len(wsCity.Value) > 0，Len(wsState.Value) =0Len(wsGender.Value) > 0，Len(wsAgeL) = 0，Len(wsAgeU) =0 strSQL = strSQL & "CFCITY = '“& UCase(wsCity.Value) &UCase(wsCity.Value)和”&_ "CFSEX =‘& wsGender &’‘

我会在一个公共模块中编写一个函数来返回SQL。该函数将通过参数获取其所有参数，而不依赖于全局变量或工作表范围。这将打破对当前工作簿结构的依赖，并使测试代码变得更容易。

Function getCFMASTSQL(City As String, State As String, DOB As Single, Gender As String, AgeLower As String, AgeUpper As String) As String
    Const BaseSQL As String = "SELECT cfna1, CFNA2, CFNA3, CFCITY, CFSTAT, LEFT(CFZIP,5) FROM CNCTTP08.JHADAT842.CFMAST CFMAST "

    Dim Wheres As New Collection

    If DOB > 0 Then
        Wheres.Add "cfdob7 = " & DOB
    Else
        Wheres.Add "cfdob7 != 0"
        Wheres.Add "cfdob7 != 1800001"
        Wheres.Add "CFDEAD = 'N'"
    End If

    If Len(AgeLower) > 0 And Len(AgeUpper) > 0 Then
        Wheres.Add "TIMESTAMPDIFF(256, CHAR(TIMESTAMP(CURRENT TIMESTAMP) - TIMESTAMP(DATE(DIGITS(DECIMAL(cfdob7 + 0.090000, 7, 0))), CURRENT TIME))) BETWEEN " & AgeLower & " AND " & AgeUpper
    ElseIf Len(AgeLower) > 0 Then
        Wheres.Add "TIMESTAMPDIFF(256, CHAR(TIMESTAMP(CURRENT TIMESTAMP) - TIMESTAMP(DATE(DIGITS(DECIMAL(cfdob7 + 0.090000, 7, 0))), CURRENT TIME))) >=  " & AgeLower
    ElseIf Len(AgeUpper) > 0 Then
        Wheres.Add "TIMESTAMPDIFF(256, CHAR(TIMESTAMP(CURRENT TIMESTAMP) - TIMESTAMP(DATE(DIGITS(DECIMAL(cfdob7 + 0.090000, 7, 0))), CURRENT TIME))) <=  " & AgeUpper
    End If

    If Len(Gender) > 0 Then
        Wheres.Add "CFSEX = '" & Gender & "'"
    Else
        Wheres.Add "CFSEX != 'O'"
    End If

    If Len(City) > 0 Then Wheres.Add "CFCITY = '" & UCase(City) & "'"
    If Len(State) > 0 Then Wheres.Add "CFSTAT = '" & UCase(State) & "'"

    Dim SQL As String

    If Wheres.Count > 0 Then
        Dim Values() As String
        ReDim Values(1 To Wheres.Count)

        Dim n As Long

        For n = 1 To Wheres.Count
            Values(n) = Wheres(n)
        Next

        SQL = BaseSQL & vbNewLine & "WHERE " & Join(Values, " AND ")
    Else
        SQL = BaseSQL
    End If

    getCFMASTSQL = SQL
End Function