Having trouble with IF condition using SQL

I cannot seem to get this IF statement to step into the Call Expiry part it goes to the else therefore I can only assume there is something wrong with my condition.

I’m trying to fetch the contract status from an access table Investment Data WHERE Customer Number is the Customer Number on the current spreadsheet. (There is only 1 customer number on spreadsheet).

Is there something wrong with my logic or does the SELECT statement return something that is not a string?

Any Help is appreciated, thanks.

 If ("SELECT [Contract Status] FROM [Investment Data] WHERE [Customer Number] =(" & "SELECT [Customer Number] FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh & ")") = "Current" Then
        expiryPrompt = MsgBox("A previous entry from the same contract is currently active, would you like to expire current active contract?", vbYesNo, "Confirmation")
        Call Expiry
        Exit Sub
    Else
        MsgBox ("Entry has been entered into the database.")
        cN.Execute ssql
        Exit Sub
    End If

Edit 1: In terms of trying to store the SELECT I have the following

Dim cdb As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim temp As String

strSQL = "SELECT [Contract Status] FROM [Investment Data] WHERE [Customer Number] =(" & _
"SELECT [Customer Number] FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh & ")"

Set cdb = DAO.OpenDatabase(dbPath)
Set rst = cdb.OpenRecordset(strSQL)
temp = rst

I know the the return value of a Recordset is not a String I just don’t know how I would store it in a variable.

Answer

You will need to put the value into something so as you can compare it in the if statement. If I understand what you want then give this a try. I am also assuming you will get one result in the SQL statement.

    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    conn.Open sConnString
    Set rs.ActiveConnection = conn


    strSQL = "SELECT [Contract Status] FROM [Investment Data] WHERE
     [Customer Number] =(" & "SELECT [Customer Number] FROM
      [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh & ")"

    rs.Open strSQL
        If Not rs.EOF Then xx = rs.GetRows Else 'do you code for not getting result 
    rs.Close

    If xx(0, 0) = "Current" Then
        expiryPrompt = MsgBox("A previous entry from the same contract is currently active, would you like to expire current active contract?", vbYesNo, "Confirmation")
        Call Expiry
        Exit Sub
    Else
        MsgBox ("Entry has been entered into the database.")
        cN.Execute ssql
        Exit Sub
    End If

I also have not amended your code inside the if statement to follow my way I accessing the database.

To answer your question in the coments. The result will be transposed into the variable. so accessing more than one row or column , you will need to do something like this.

For getting the rows,

        For i = 0 To 9

          Cells(i + 1, 1) = x(0, i)

        Next i

For getting the columns,

        For i = 0 To 9

          Cells(1, i + 1) = x(i, 0)

        Next i