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

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
788
Office Version
  1. 365
Platform
  1. Windows
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 & "'

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

Call sbADO2(REGION1, SQLSTR)
End Sub


PHP:
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
73705c3ce0.png
1b8a84143c.png


a38297eff3.png


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

Does anyone know what the problem is?

Appreciate any help
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi JumboCactuar,

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

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

...whereas it should be this:

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

HTH

Robert
 
Last edited:
Upvote 0
@Trebor76 thanks for the reply

ive tried this and it throws an error:
17a621148d.png



at this stage of the code, the sql string looks perfectly correct
3dc95c7e0a.png


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

see if i hardcode the region 1 into the string, it outputs fine
e0579a10c0.png
 
Last edited:
Upvote 0
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):

Code:
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
 
Upvote 0
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):

Code:
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
 
Upvote 0
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.
 
Upvote 0
@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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top