Skip to main content
Code Review

Return to Question

explained what the code does.
Source Link
Zack E
Zack E
  • 595
  • 2
  • 9

This code runs a SQL search from a IBM AS/400 server based on the criteria entered in by a user on a UserForm.

This code runs a SQL search from a IBM AS/400 server based on the criteria entered in by a user on a UserForm.

placed all code into code blocks
Source Link
Zack E
Zack E
  • 595
  • 2
  • 9
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

Other Procedures From UserForm:

 
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

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

Other Procedures From UserForm:

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

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
added 4 characters in body
Source Link
Mathieu Guindon
Mathieu Guindon
  • 75.6k
  • 18
  • 195
  • 469
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

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

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

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
updated question with all procedures.
Source Link
Zack E
Zack E
  • 595
  • 2
  • 9
fixed syntax highlighting
Source Link
Mathieu Guindon
Mathieu Guindon
  • 75.6k
  • 18
  • 195
  • 469
Source Link
Zack E
Zack E
  • 595
  • 2
  • 9