SQL.REQUEST and Excel 1

[mmcardle]

I have been trying to use the Excel ODBC add-in function SQL.REQUEST with limited and frustrating results.
I've got the function to work with an Informix database, but I can't get it to return data from a query
that references a cell on the worksheet.

For instance, this works fine:

=SQL.REQUEST("DSN=test;UID=odbc;PWD=pwd",,2,"SELECT supplier, name FROM plsupp WHERE supplier = 'CUS001'&quot

This correctly returns the supplier code and name for supplier CUS001.

But! If I alter the function to pick up the supplier code from a cell on the worksheet like this:

=SQL.REQUEST("DSN=test;UID=odbc;PWD=pwd",,2,"SELECT supplier, name FROM plsupp WHERE supplier = " &
I20)

All I get back is #N/A (I20 contains CUS001).

I've read all the online help and scoured the Internet for other information and as far as I can tell
I'm doing this right. But, it won't %$#~(^&$ work!

Any kind soul out there who knows the answer to this?

 

[MaffewW]

Hi guys, I would presume this thread is dead but maybe now someone can help. I am having similar troubles when trying to use this add-in

I am successfully retrieving data from a SQL Server database with the syntax

=SQL.REQUEST("DSN=alicolive;UID=mwilson; PWD='test';Database=eic_dialer",, 2, "select count (*) from callhistory", FALSE)

when I specify 'where criteria' eg

=SQL.REQUEST("DSN=alicolive;UID=mwilson; PWD='test';Database=eic_dialer",, 2, "select count (*) from callhistory where calldate = '19 august 2002'", FALSE)

I get a successful result, however when I use a cell as a reference eg

=SQL.REQUEST("DSN=alicolive;UID=mwilson; PWD='test';Database=eic_dialer",, 2, "select count (*) from callhistory where calldate = "&J1&"", FALSE)

I get '0' as a result. J1 is formatted to date. I have used cell references in other queries successfully, it seems to be a problem with the way the date is being parsed. I'm not sure if anyone will be able to help with this useful but poorly documented function, maybe someone has come across something elsewhere on the internet?

Thanks

MW

 

[xlbo]

Should it not be:
where calldate = "'" & J1 & "'", FALSE)

I think dates have to be surrounded by ' ' to be defined as a date and not text.... Rgds
~Geoff~

 

[MaffewW]

Thanks for the reply Geoff

After much trial and error I think we're there, you were right about the '

=SQL.REQUEST("DSN=alicolive;UID=mwilson; PWD='test';Database=eic_dialer",, 2, "select count (*) from callhistory where calldate = '"&J1&"'",FALSE)

but the cell in Excel has to be formatted as a text field before it is passed to the datetime field in SQL

Matt

 

[xlbo]

Ah yes - that makes sense - in Excel, a date is actually a serial number which is then FORMATTED to look like a date - hence, when you pass a cell value into any code, you need to use either the FORMAT function within VBA or the TEXT function within the worksheet
Glad you got sorted
Rgds
~Geoff~

 

[dvcf1n]

My entire Data is in EXCEL.I shall try it out. Thanks.