You know, I had forgotten about not using expressions in SQL statements for ORDER BY. In regard to using reserved words, I try not to, but in this case, my predecessor had already created the tables years before I took over from him.
I have revised my code, and doing so presents me with another question:
How do I use the below code, and enhance it to eliminate any matches between blank PN fields in the two tables, in regard to the Remnant Description field? The below code gives me lots of results, but I am not sure that they are all correct. I just wanted to pick up a Description any time there was a match between the Scrap and remnant tables, otherwise, leave it blank.
lcStartMMDD=LEFT(DTOC(.txtBegDate.Value), 5)
lcEndMMDD=LEFT(DTOC(.txtEndDate.Value), 5)
lcYY=SUBSTR(DTOC(.txtBegDate.Value), 9, 2)
DO CASE
CASE .OptGrp1.optTotalsOnly.VALUE = 1
SELECT ;
S.Emp, S.Dept, S.Netwgt, S.Date, S.PN, ;
R.Descriptn ;
FROM ;
SCRAP S, ;
REMNANT R ;
WHERE ;
S.PN = R.PN AND ;
LEFT(S.DATE,5)=>lcStartMMDD AND ;
LEFT(S.DATE,5)<=lcEndMMDD AND ;
SUBSTR(S.DATE,7,2)=lcYY AND ;
S.remnant ;
ORDER BY ;
2, 4 ;
INTO TABLE ;
C:\SCRAP\REMNANT_RPT
SELECT REMNANT_RPT
COPY TO "C:\SCRAP\Remnants_" + lcDate TYPE XLS
USE
>Don't use thisform properties, use local variables. You also can not use expression in ORDER BY, it should be a column.
>Not to mention that it is not a good idea of using reserved VFP word as a field name and a name DATE for a character field <g>.