Double single quote issue

[JICGreg]

I'm missing something, what I think is easy, here that hopefully someone can help me with

I'm connecting to an excel 2007 file to bring in data. The following code, when hardcoded works (there is obviously more to the code but this is the relevant part)

store '"2009_3_distinct_portfolios$"' TO sheetname
lcSQLCmd = [Select * FROM &sheetname]

I may be hard to see on the screen so it is single quote, double quote, 2009....., doublequote, singlequote

Problem, is I need the flexability to change the sheetname as dates change. So I have created:

STORE '"' + ALLTRIM(STR(year)) + "_" + alltrim(STR(month)) + "_distinct_portfolios$" + '"' TO sheetname1

(double quote + year_month_distinctportfolios + doublequote


STORE "'" + sheetname1 + "'" to sheetname

(single quote + sheetname1 + single quote

When I print it out to the screen, it looks like the hardcoded version. However, it does not work in the select statement. My best guess is that it has something to do with the single and/or double quotes.

Can anyone provide help












Problem is it is data specfic so I am trying to change it to be date agnostic. The code I came up with is this:



STORE '"' + ALLTRIM(STR(year)) + "_" + alltrim(STR(month)) + "_distinct_portfolios$" + '"' TO sheetname1

STORE "'" + sheetname1 + "'" to sheetname



It looks like (whne I print it to the screen) it should work. But it does not. Can you tell me what i'm doing wrong.



Greg

 

[bborissov]

Round it with square brackets and do NOT use macro:

STORE '["' + ALLTRIM(STR(year)) + "_" + alltrim(STR(month)) + "_distinct_portfolios$" + '"]' TO sheetname1

lcSQLCmd = [Select * FROM ] + sheetname
...

The result should look like this:

["2009_1_distinct_portfolios$"]

That is ONLY if you have double quotes in the name, if you do not:

STORE '[' + ALLTRIM(STR(year)) + "_" + alltrim(STR(month)) + "_distinct_portfolios$" + ']' TO sheetname1

lcSQLCmd = [Select * FROM ] + sheetname

The result should look like this:

[2009_1_distinct_portfolios$]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Olaf Doschke]

Hi Greg,

with the "hardcoded" version you only have single quotes within the string value. With your dynamic version you put single and double quotes WITHIN the string value, that is what's wrong.

Remove the line

STORE "'" + sheetname1 + "'" to sheetname

Because if you do

store 'test' to lcString
? lcString

You also don't get a display of 'test', but ? will show test without the single quotes.

Or with your orignal store:

store '"2009_3_distinct_portfolios$"' TO sheetname
? sheetname

What ? displays is your goal, this must be the value inside sheetname.

string delimiters in code like store 'stringliteral' to stringvariable are there to mark what is the string within code, but the string value is what is inside the string delimiters.

Bye, Olaf.