adding the contents of a memo field to a report using SQL - SELECT 1

[eldredn]

I want to export some data from 3 tables into an Excel spreadsheet. I've included the SELECT command below. Everything is fine except IT ONLY EXPORTS THE FIRST CHARACTER FROM THE staff_list.notes FIELD (which is a memo field with the phone extension). Note: I put the extensions in a memo field since only about 100 of the 400 employee records actually have extensions. If I had created it as a field, most of the records would be empty.

SELECT DISTINCT trim(lastname) AS "LASTNAME", trim(firstname) AS "FIRSTNAME", trim(e_mail) AS "EMAIL", trim(position) AS "TITLE",trim(market.market) AS "MARKET",trim(offices.phone1) AS "OFFICEPHONE", staff_list.notes  AS "EXT", trim(staff_list.phone2) AS "HOMEPHONE";
 FROM Staff_list,Offices,Market;
 WHERE SUBSTR(Offices.office_no,1,3) = SUBSTR(Staff_list.office_no,1,3);
 AND staff_list.market = market.market;
 AND NOT EMPTY (staff_list.phone1);
 AND BETWEEN(val(trim(staff_list.office_no)),3,10)=.f. = .T.;
INTO TABLE "Eldred:Desktop Folder:stafflist.xls";
ORDER BY LASTNAME

[sig][/sig]

 

[foxdev]

I'm surprised even one character appears.

1) change the clause in your Select statement to read:
[tt]left(staff_list.notes,5) AS EXT, [/tt]

2) you probably didn't save much by making it a memo field. A memo field still uses 4 bytes in the DBF for a pointer into the FPT memo file. If your extensions are 4 characters, then its even either way, but not having a memo is obviously less of a hassle and more reliable. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[foxdev]

...and if you are using FoxPro (not Visual), then the memo field pointer in the DBF consumes 10 bytes. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[eldredn]

Thanks for the quick response, foxdev. Unfortunately, &quot;left(staff_list.notes,4)&quot; didn't work. Again, only the first digit appeared in the report. I even tried saving it to a .csv instead of .xls and opening it in Word.

The thing is, typing &quot;? left(staff_list.notes,4)&quot; into the command window returns the correct 4-digit extension in the main FoxPro window.

So, since that has failed....(unless you have another suggestion)... how do I export all the information in the memo fields to a regular field (and have it associated with the correct record)?

Thanks.

P.S. I'm using an old version (2.5b for Macintosh). [sig][/sig]

 

[foxdev]

I've never worked with the Mac version, so I'm flying a bit blind. Since you didn't get a syntax error, but still only got one character, my second guess is that the SELECT statement processor is quirky about how it determines field lengths, so try this:

left(staff_list.notes, 4) + space(4) as WhatEver...

Make sure you are not using TRIM for this particular clause (though you can use it for others).
[sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[foxdev]

Eldredn, I was just looking back over your original post and your original SQL statement when I noticed this line:

[tt]AND BETWEEN(val(trim(staff_list.office_no)),3,10)=.f. = .T.;[/tt]

I'm not quite sure what your intention was, but it could probably be better expressed as:

[tt]AND BETWEEN(val(staff_list.office_no), 3, 10) ;[/tt]
[sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[eldredn]

That worked!!!! Thanks a lot. genius.

eldredn@aquent.com [sig][/sig]

 

[eldredn]

I know it looks convoluted.

BETWEEN(val(trim(staff_list.office_no)),3,10)=.f. = .T.

allows me to select the records that fall outside of the range 3-10 (in this case 445 records).

BETWEEN(val(staff_list.office_no), 3, 10)

selects the records that fall within the range (the 17 records I want to exlude from the query results).

I realize now I could have omitted the '=.T.' at the end. Thanks again for the solution to the memo problem.

[sig][/sig]

 

[foxdev]

BETWEEN(val(trim(staff_list.office_no)),3,10)=.f. = .T.

allows me to select the records that fall outside of the range 3-10 (in this case 445 records).

If you want to select the ones that are NOT between 3 and 10 (IOW, negate the condition), then this would be the cleaner way:

[tt]NOT BETWEEN(val(trim(staff_list.office_no)),3,10[/tt] [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]