SQLExecQuery help

MPullen

New Member
Joined
Apr 8, 2002
Messages
13
I'm using the SQLExecQuery command to pull data from my database. But I want to use a cell within the Excel worksheet as part of the query.
ie.
"SELECT CLIENTS.NAME FROM DATA.CLIENTS WHERE CLIENTS.CLIENT_ID = ??"

Where ??, I'd like to reference cell B4.
Is this possible?
What's the formula?
Thanks
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
="SELECT CLIENTS.NAME FROM DATA.CLIENTS WHERE CLIENTS.CLIENT_ID = '"&B4&"'"
This message was edited by Mark W. on 2002-04-09 16:18
 
Upvote 0
Thanks for the help Mark, but I tried your suggestion and I can't get it to work.

Have you any other suggestions please?

Thanks
 
Upvote 0
I always tie myself in knots with quotation marks but you could try...

"SELECT CLIENTS.NAME FROM DATA.CLIENTS WHERE CLIENTS.CLIENT_ID = " & Range("B4").Value

(I'm not sure whether the B4 needs a double set of quotes on either side??)

Or, have a variable...

myClientID = Range("B4").Value

Then

"SELECT CLIENTS.NAME FROM DATA.CLIENTS WHERE CLIENTS.CLIENT_ID = " & myClientID
 
Upvote 0
Sorry, I didn't recognize this as a VBA command... and, confused it with the SQL.REQUEST worksheet function. VBA isn't my forte, but couldn't you assign Rang.("B4") to a variable and concatenate that to your string? Like they did with "chan" in the example from the SQLExecQuery Function help topic...

databaseName = "Northwind"
queryString = _
"SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)"
chan = SQLOpen("DSN=" & databaseName)
SQLExecQuery chan, queryString
Set output = Worksheets("Sheet1").Range("A1")
SQLRetrieve chan, output, , , True
SQLClose chan
 
Upvote 0
You help does make sense, but I still can't get it to work.

If I enter a CLIENT_ID ie 'GW0004' into the script, it works fine.
But not when I try to reference a cell on the worksheet.

Here's all the code, just incase I've screwed up somewhere else.
Your continuing help is much appricated.

Sub LeaseName()
Dim DB As Variant
Dim MYID As Variant

MYID = Range("B5").Value

DB = SQLOpen("DSN=Database;UID=User;PWD=Password")

SQLExecQuery DB, "SELECT CLIENTS.NAME FROM DATA_MERGED.CLIENTS CLIENTS WHERE CLIENTS.CLIENT_ID = " & MYID

SQLRetrieve DB, ActiveCell

SQLClose (DB)

End Sub
 
Upvote 0
This is beginning to bug me coz I know it can work! I do the same thing with a mySQL database and have no problems.
My exact code (well, relevant bits anyway) is below...



Dim Chan As Variant
Dim MyBase As Variant
Dim myCompany As Variant

MyBase = "DSN=alexismedical;DB=alexismedical;SERVER=10.10.10.254;UID=xxxx;PWD=xxxx;PORT=;OPTION=3098;STMT=;"

Range("D" & currentrow).Select
myCompany = ActiveCell.Value


Chan = SQLOpen _
(MyBase, , 2)

SQLExecQuery Chan, _
"SELECT company_id, lookup_label FROM company_lookup WHERE lookup_label='" & myCompany & "' "

SQLRetrieve Chan, ActiveSheet.Range("E" & currentrow), , , False, False, False, False

SQLClose (Chan)



Now, the only discernible difference I can see is that I wrap my variable in the SQL statement with 'single' quotes.

Try this. If it doesn't work then I think I'm well stumped.

Rgds
AJ
 
Upvote 0
Cool!
Very pleased it worked!

And, just for your information, I've just found out why (thanks to a know-it-all mate sat a few desks away!).

For any ANSI compliant database system when you write a query with alpha or alphanumerics in it, you have to enclose them in single quotes so that it can tell whether you are looking for a value in a column or an another actual column itself in the table.

For example, if you say

SELECT ... FROM TABLE... WHERE COLUMNX=BLAHBLAHBLAH,

it will look for a column BLAHBLAHBLAH and see if the value in COLUMNX equals it!

However, if the BLAHBLAHBLAH is in quotes, it will look for the item BLAHBLAHBLAH within COLUMNX.

For numeric stuff though, it's cool (as long as the properties of that field in the table is defined as numeric of course).

The fact that Excel is the database interface is irreleveant when using the xlodbc.xla addin. It sends the query to the database exactly as if you'd done it to the database directly.

Enough of my ramblings!
Hope this makes sense!

Cheers
AJ
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

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