mYxCHa mYxCHa - 4 months ago 18
SQL Question

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