Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Miscellaneous

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
38%
Installation Wizard into new VRC
41%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 37

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 12th February 2002, 18:53
rosalbaperez rosalbaperez is offline
Member
 
Join Date: Oct 2001
Location: San Diego
Posts: 32
rosalbaperez is on a distinguished road
Baan: IVc4, SP4 - DB: MS SQL 2000 - OS: WIN 2K
Extract data from Baan into an Excel cell

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
Reply With Quote
  #2  
Old 12th February 2002, 19:34
evesely's Avatar
evesely evesely is offline
Guru
 
Join Date: Dec 2001
Location: Lisle, Illinois USA
Posts: 411
evesely is on a distinguished road
A Guess

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)?
Reply With Quote
  #3  
Old 12th February 2002, 21:54
rosalbaperez rosalbaperez is offline
Member
 
Join Date: Oct 2001
Location: San Diego
Posts: 32
rosalbaperez is on a distinguished road
Baan: IVc4, SP4 - DB: MS SQL 2000 - OS: WIN 2K
Extract data from Baan into an excel cell

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
Reply With Quote
Sponsored Links
  #4  
Old 13th February 2002, 00:15
evesely's Avatar
evesely evesely is offline
Guru
 
Join Date: Dec 2001
Location: Lisle, Illinois USA
Posts: 411
evesely is on a distinguished road
Got it...

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
Code:
LIKE 'cha%'
matches words starting with cha.

I hope this helps.

-Ed
Reply With Quote
  #5  
Old 13th February 2002, 02:39
rosalbaperez rosalbaperez is offline
Member
 
Join Date: Oct 2001
Location: San Diego
Posts: 32
rosalbaperez is on a distinguished road
Baan: IVc4, SP4 - DB: MS SQL 2000 - OS: WIN 2K
THANKS !!!

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

Thanks again!

Rosalba
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Urgent Help...sending RFID data to Baan as a session vig_varun Third Party Products 3 30th April 2004 18:40
Baanboard.com Community Bulletin 01/05/2002 patvdv About Baanboard.com 0 1st May 2002 16:04
BSP-US (LLC) added as a Baan Education Alliance svandenh General Discussion & Chat 1 20th August 2001 20:44


All times are GMT +2. The time now is 17:23.


©2001-2017 - Baanboard.com - Baanforums.com