Can anyone figure out or explain why this code doesnt work? (passing SQL string)

[JumboCactuar]

Hi,
cant figure out whats wrong here:

This Works with the following SQL string in cell P9
SELECT * From [DataSheet$] WHERE Region='UK'

But it fails with this, where the Region is in cell P7
SELECT * From [DataSheet$] WHERE Region='" & REGION1 & "'

Sub Test2()
Dim REGION1 As String, SQLSTR As String
REGION1 = Range("P7")
SQLSTR = Range("P9")

Call sbADO2(REGION1, SQLSTR)
End Sub

Sub sbADO2(REGION1 As String, SQLSTR As String)
Dim sSQLQry As String
Dim ReturnArray

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset

Dim DBPath As String, sconnect As String


DBPath = ThisWorkbook.FullName

sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    
Conn.Open sconnect
    sSQLSting = SQLSTR  

    mrs.Open sSQLSting, Conn

    ActiveSheet.Range("A2").CopyFromRecordset mrs

    mrs.Close

Conn.Close

End Sub

The code completes without errors, though no data is output

Ive run through the code and it looks fine

(Ignore screenshots where it's calling SBADO I changed this)

Does anyone know what the problem is?

Appreciate any help

 

[JumboCactuar]

Bump

 

[Trebor76]

Hi JumboCactuar,

I think you've got the second single quote in the wrong place, i.e. you have this...

SELECT * From [DataSheet$] WHERE Region='" & REGION1 & "[B]'[/B]

...whereas it should be this:

SELECT * From [DataSheet$] WHERE Region='" & REGION1 & "[B]'[/B]"

HTH

Robert

 

[JumboCactuar]

@Trebor76 thanks for the reply

ive tried this and it throws an error:

at this stage of the code, the sql string looks perfectly correct

its like its not accepting this passed from another sub " & REGION1 & "

see if i hardcode the region 1 into the string, it outputs fine

 

[Trebor76]

In your original code (Test2) you don't qualify which sheet the variables REGION1 or SQLSTR are coming from so they will be referencing cells P7 and P9 from whatever sheet is active when the code is run. See if the following resolves the issue (change the sheet name from DataSheet in the code if necessary):

Option Explicit
Sub Test2()
    
    Dim REGION1 As String, SQLSTR As String
    
    With Sheets("DataSheet")
        If Len(.Range("P7")) = 0 Then
            MsgBox "No region has been entered." & vbNewLine & "Please enter one in cell P7 of the ""DataSheet"" tab and try again.", vbCritical
            Exit Sub
        ElseIf Len(.Range("P9")) = 0 Then
            MsgBox "No SQL statement has been entered." & vbNewLine & "Please enter one in cell P9 of the ""DataSheet"" tab and try again.", vbCritical
            Exit Sub
        End If
        REGION1 = .Range("P7")
        SQLSTR = .Range("P9")
    End With

    Call sbADO2(REGION1, SQLSTR)
    
End Sub

Regards,

Robert

 

[JumboCactuar]

In your original code (Test2) you don't qualify which sheet the variables REGION1 or SQLSTR are coming from so they will be referencing cells P7 and P9 from whatever sheet is active when the code is run. See if the following resolves the issue (change the sheet name from DataSheet in the code if necessary):

Option Explicit
Sub Test2()
    
    Dim REGION1 As String, SQLSTR As String
    
    With Sheets("DataSheet")
        If Len(.Range("P7")) = 0 Then
            MsgBox "No region has been entered." & vbNewLine & "Please enter one in cell P7 of the ""DataSheet"" tab and try again.", vbCritical
            Exit Sub
        ElseIf Len(.Range("P9")) = 0 Then
            MsgBox "No SQL statement has been entered." & vbNewLine & "Please enter one in cell P9 of the ""DataSheet"" tab and try again.", vbCritical
            Exit Sub
        End If
        REGION1 = .Range("P7")
        SQLSTR = .Range("P9")
    End With

    Call sbADO2(REGION1, SQLSTR)
    
End Sub

Regards,

Robert

Thankyou I will try this, though this likely won't work as the variables are on the same sheet as intended. Plus when I F8 through the code and hover over them, they show correct

 

[RoryA]

It looks to me like you're passing REGION1 as a variable and a SQL string that actually includes REGION1 as a literal. That won't work. You need to concatenate REGION1 into your SQL string and then pass just that as an argument.

 

[JumboCactuar]

@RoryA
That's done it thankyou

I build the region variable into the SQL string using CONCATENATE before passing it to the sub and it's working correctly now