SQL Query (ODBC) with Cell Reference

R

ricksan

New Member
Joined
Sep 15, 2009
Messages
2
I have a SQL query (through an ODBC connection) that populates an Excel spreadsheet. The thing is that I want add a criteria "WHERE x = [cell reference]". I know this question has been asked repeatedly in different forms but it seems that connecting with Excel 2007 through an ODBC connection doesn't behave the way others do. I've tried using the following syntax:

WHERE A.EMPLID = '" & Range("B1") & "'

A.EMPLID is my SQL field name
B1 is the cell where I enter my A.EMPLID filter value

If I manually edit the query (as below) it returns a perfect set of data. I'm just trying to avoid having to manually edit the query every time I want a new data set.

WHERE A.EMPLID = '81726354'

The query with the cell reference doesn't cause any errors but it doesn't return any results either. I've also named the cell as a range but don't know how I'd reference that in a SQL query (if that's even possible). I'm trying to avoid doing it with VBA.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
My first guess is that the quote marks around B1 in the following could be causing some SQL issues.

WHERE A.EMPLID = '" & Range("B1") & "'

Perhaps you could try creating a variable to hold the B1 value; something like this.

Dim myCriteria As String

myCriteria = Trim(Range("B1").Value)
WHERE A.EMPLID = '" & myCriteria & "'
 
Upvote 0
I don't know how or why the code below pulls SQL from cell ref but when I step into a macro created by a former team member, the data import works every time. I have been trying to figure out what Chr(13) ref but maybe it is the quote (") structure is structured? I hope this helps.

'SQL
' MsgBox ("SELECT JDE_Transactions_Remapped.*" & Chr(13) & "" & Chr(10) & "FROM ""O-Warehouse"".dbo.JDE_Transactions_Remapped" & Chr(13) & "" & Chr(10) & _
"WHERE (" & TimePer & ") AND(" & Acct & ") AND " & _
"(" & Location & ")" & " AND (" & Dept & ") AND (" & LOB & ") AND (" & LU & ")")
 
Upvote 0
Code: 
WHERE A.EMPLID = [B][COLOR="red"]'[/COLOR][/B]" & Range("B1") & "[B][COLOR="Red"]'[/COLOR][/B];"
 
Upvote 0
ricksan said:
I have a SQL query (through an ODBC connection)... it seems that connecting with Excel 2007 through an ODBC connection doesn't behave the way others do.
Click to expand...
It should. Can you post the entire statement or set of statements where you build your SQL query string? The problem may be a mismatched symbol outside the code you've posted.

PS. The quote marks around B1 shouldn't cause any issues provided all the other single and/or double quotes are correct since SQL will never see them. Chr(10) and Chr(13) are ignored by SQL.
 
Upvote 0
You should end up with something like this:-
Code: 
strSQL=[COLOR=red][B]"[/B][/COLOR]SELECT * FROM tblSomething WHERE A.EMPLID = [B][COLOR=blue]'[/COLOR][COLOR=red]"[/COLOR][/B] & myCriteria & [B][COLOR=red]"[/COLOR][COLOR=blue]'[/COLOR][/B] ORDER By A.EMPLID;[COLOR=red][B]"[/B][/COLOR]
or:-
Code: 
strSQL=[COLOR=red][B]"[/B][/COLOR]SELECT * FROM tblSomething WHERE A.EMPLID = [B][COLOR=blue]'[/COLOR][COLOR=red]"[/COLOR][/B] & myCriteria & [B][COLOR=red]"[/COLOR][COLOR=blue]'[/COLOR][/B][COLOR=red][B]"[/B][/COLOR]
If you place a breakpoint after the strSQL= statement, you can open the Immediate window (Ctrl-G) when the code pauses and display the contents of the string variable by typing ?strSQL and hitting Enter. That may well give you an obvious clue as to where the problem lies.

But if you post your code, I'll take a look at it.

I'm assuming that EMPLID is a text field, since you're wrapping it in quotes? If it's numeric, you should end up with a string that looks like this:-
Code: 
strSQL=[COLOR=red][B]"[/B][/COLOR]SELECT * FROM tblSomething WHERE A.EMPLID = [B][COLOR=red]"[/COLOR][/B] & myCriteria & [COLOR=red][B]"[/B][/COLOR][COLOR=black];[/COLOR][COLOR=red][B]"[/B][/COLOR]
or, without the semicolon:-
Code: 
strSQL=[COLOR=red][B]"[/B][/COLOR]SELECT * FROM tblSomething WHERE A.EMPLID = [B][COLOR=red]"[/COLOR][/B] & myCriteria

(I always include the semicolon.)
 
Upvote 0
So I stumbled across the need to reference a parameter in a SQL Query using Excel 2007 and here is what I found.

In your WHERE clause you can specify a Question Mark (?) which will force a Parameter window to open.

Select * from Mytable Where DateTime >= ?

When this parameter window opens you have the ability to reference a cell or just enter the value you want to use. You also have some other cool functionality around the parameter option.

It's Pretty cool and easy to use. The hardest part was finding it.

Hope this helps
Charlie
 
Upvote 0
Hi All, New to this forum and SQL,
Found the post above and all was working fine, problem is when i closed the file and reopened it, hit refresh, Excel hangs up and says the file is corrupt.
Any ideas.
 
Upvote 0
Say pannu.gs, I know it's been a while since this post and you may already have found this out, but the Chr(13) and Chr(10) references are ASCII references to CR and LF (Carriage Return and Line Feed) respectively. Used in a line of code as part of a text string you can format the output of the text as it goes into the receiving container or object. This works in MS Excel and VB but (as noted before) may not be recognized by some other languages (like SQL).
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Power Query and cell formatting
2
Replies
10
Views
272
Peter_SSs
Peter_SSs
A
  • Question
Using VLOOKUP with a connection to SQL instead of a query
Replies
0
Views
884
apor
A
D
  • Question
Newbie to data connections...
Replies
1
Views
170
Kerryx
K
gravanoc
  • Question
Power Query & VBA Issue
Replies
1
Views
316
GraH
GraH
J
  • Question
VBA ADODB query Excel workbook as source, how to join?
Replies
3
Views
2K
Jaymond Flurrie
J

Forum statistics

Threads
1,215,054
Messages
6,122,893
Members
449,097
Latest member
dbomb1414

Share this page

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