Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
#N/A Using SQL.REQUEST
Hi folks,
I'm using SQL.REQUEST to query MSDE 2000 With Excel 2002. Normally this works splendidly, but I have one table that is giving me fits. Using the following string (or similar): =SQL.REQUEST("DSN=AF;UID=***;PWD=***;DATABASE=Cust omers",,2,"SELECT SourceName FROM tblSourceRef WHERE Code="&$P$11&"") I simply get #N/A returned to the cell. I've tried many, many variations to get to the bottom of this. The table I'm querying from originally had a space in the name (tblSourceRef was TBL SourceRef) and I thought maybe that was my problem, but alas, no. I can use the same worksheet, and the same basic statement, to collect any other data from any other table in this database. I have also tried replacing the variable P11 with a static value. I have used MS Query to query this table, and it has worked fine. I have also tried the fully qualified names (i.e., "Customers.dbo.tblSourceRef") but to no avail. What am I missing here? The object is to type a customer "Code" into a cell and return the "SourceName", "Address", and other info one piece at a time into separate cells. -- Mike Brown Process Manager Asset Forwarding Corp. EPA-compliant Recycling DoD 5220.22-M Data Elimination http://www.assetforwarding.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
#N/A Using SQL.REQUEST
Hi Mike,
Is the column "Code" a text-based column? If so, you may need to wrap the value with single quotes: "SELECT SourceName FROM tblSourceRef WHERE Code='" & $P$11 & "'" -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Mike Brown wrote: Hi folks, I'm using SQL.REQUEST to query MSDE 2000 With Excel 2002. Normally this works splendidly, but I have one table that is giving me fits. Using the following string (or similar): =SQL.REQUEST("DSN=AF;UID=***;PWD=***;DATABASE=Cust omers",,2,"SELECT SourceName FROM tblSourceRef WHERE Code="&$P$11&"") I simply get #N/A returned to the cell. I've tried many, many variations to get to the bottom of this. The table I'm querying from originally had a space in the name (tblSourceRef was TBL SourceRef) and I thought maybe that was my problem, but alas, no. I can use the same worksheet, and the same basic statement, to collect any other data from any other table in this database. I have also tried replacing the variable P11 with a static value. I have used MS Query to query this table, and it has worked fine. I have also tried the fully qualified names (i.e., "Customers.dbo.tblSourceRef") but to no avail. What am I missing here? The object is to type a customer "Code" into a cell and return the "SourceName", "Address", and other info one piece at a time into separate cells. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
#N/A Using SQL.REQUEST
"Jake Marx" wrote in message
... Hi Mike, Is the column "Code" a text-based column? If so, you may need to wrap the value with single quotes: "SELECT SourceName FROM tblSourceRef WHERE Code='" & $P$11 & "'" -- Regards, Jake Marx MS MVP - Excel www.longhead.com Jake, That was it. *Kicks self* Thanks for the help, in less than 10 minutes at that! Mike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
#N/A Using SQL.REQUEST
Mike Brown wrote:
"SELECT SourceName FROM tblSourceRef WHERE Code='" & $P$11 & "'" That was it. *Kicks self* Thanks for the help, in less than 10 minutes at that! No problem, Mike - glad to help. Sometimes we just need a fresh set of eyes to find bugs. <g -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If then request | Excel Worksheet Functions | |||
Request | Excel Worksheet Functions | |||
sql.request | Excel Discussion (Misc queries) | |||
CHR( ) request | Excel Worksheet Functions | |||
2nd request | Excel Programming |