Hello,
I am trying to extract the standard cost of an specific item, from Baan into an excel cell. Here is the command that I am using in the cell, and cell A2 contains the item key (eg. R-S135).

=SQL.REQUEST("DSN=Baan;UID=xxxxx;PWD=xxxxx;Database=baandb"||2| "SELECT a.t_copr FROM ttiitm001100 a WHERE a.t_item LIKE "&$A$2&""|FALSE)

I get a #N/A value instead. I tried to use TRIM, LTRIM and RTRIM without success. If I replace the "&$A$2&" with 'R-S135' on the command, the command works perfect.

Any suggestions?
Thanks!

Rosalba

Could it be a problem with nesting quotation marks within a string? Your cell reference ("&$A$2&") is already inside a string delimited by quotes. I'm just guessing here, but could you do '&$A$2&' or ""&$A$2&"" instead (in this latter case, you would then have 3 quotation marks in a row -- two to mean put a quote inside the string and one to delimit the string)?

If I use '&$A$2&' the command understands that this is the actual string, so I can not use it.

The ""&$A$2&"" does not work either, I get the #N/A message. I also tried to use the quotations in the actual cell where I am storing the item key and is not working either.

I got this format "&$X$X&" to change queries dynamically from the excell help and it is working fine on other queries where the value is a number.

I also make sure that the cell has a "text" format and still is not working.

Thanks for your answer evesely, good try! Thanks.

Rosalba

The problem was that the like clause, as formed, was no enclosed in single quotes. So, if you try the following, I think it will work:

=SQL.REQUEST("DSN=Baan;UID=xxxxx;PWD=xxxxx;Database=baandb"||2| "SELECT a.t_copr FROM ttiitm001100 a WHERE a.t_item LIKE '"&$A$2&"'"|FALSE)

It may be hard to read, so here is the last part expanded:



'"&$A$2&"'"

You probably already know this, but I'll throw it out anyway. In SQL Server LIKE clauses, use % to mean any characters. Thus
LIKE 'cha%'
matches words starting with cha.

I hope this helps.

-Ed

Hi!
It is working perfectly! Thank you so much!
I completely forgot about these quotes! :)

Thanks again!

Rosalba