SQLExecQuery help

[MPullen]

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

[Mark W.]

="SELECT CLIENTS.NAME FROM DATA.CLIENTS WHERE CLIENTS.CLIENT_ID = '"&B4&"'"


[ This Message was edited by: Mark W. on 2002-04-09 16:18 ]

[MPullen]

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

[AJ]

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

[Mark W.]

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

[MPullen]

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

[AJ]

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

[MPullen]

Thanks for help...
Works great!!!

[AJ]

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