>Work is a keyword (it's in blue in VFP editor). That could be causing your problem.
Work is a four letter word. Wash your mouth out.
>
>>Okay, here is all of the code, including some code commented out. The purpose of this code is to take deep records and make them wide (on one record), until you hit another numeric part number; if it is an alpha part number its info goes on the same record a the first numeric part number. I know, this is weird. Trust me. I know. This is what the client wants. They have an old FoxBse program which takes the data in the format I have explained and prints out a job card layout.
>>
>>
>>LOCAL lcExcelFileName AS Character
>>
>>WITH ThisForm
>> * Dump the data from the EXCEL FIle into the WORK table.
>> SELECT WORK
>> lcExcelFileName = .cExcelFileName
>> APPEND FROM (lcExcelFileName) SHEET ALLTRIM(.cboTabNames.Value) TYPE XL5
>>
>> * Remove the row that contains the Field Names.
>> SET ORDER TO
>> GO TOP
>> DELETE
>> PACK
>> SET ORDER TO ORDER_NUM
>>
>> * Create table containing a list of Order Numbers, which we will use
>> * to cycle through the WORK table by Order Number. Why? So that we can
>> * go through all line items in the order.
>> SELECT Order_Num, COUNT(LINE_ITEM) AS LINE_ITEMS ;
>> FROM WORK ;
>> GROUP BY ORDER_NUM ;
>> INTO TABLE C:\KEITH\tmpOrders
>>
>> SELECT tmpOrders
>> INDEX ON ORDER_NUM TAG ORDER_NUM
>> LOCAL lnColIndex AS Number
>> lnColIndex = 0
>>
>> *SELECT tmpOrders
>> *SCAN
>> SELECT WORK
>> SCAN
>> SELECT BatchN
>> lcOrderNo =Work.Order_Num
>> lcPart_Num=Work.Part_Num
>>
>> *SELECT BATCHN
>> IF !SEEK(lcOrderNo)
>> * If there is no record yet, make a new one.
>> APPEND BLANK
>> * Put in the data for the first line item.
>> *** 10/06/2009 Cecil Champenois. Fixing date format
>> *** Order_Date and Due_Date into format yy/mm/dd.
>> *lnColumnIndex=Work.Line_Item
>> REPLACE ;
>> BatchN.Line_Item WITH Work.Line_Item, ;
>> BatchN.Order_Num WITH Work.Order_Num, ;
>> BatchN.Order_Date WITH ;
>> SUBSTR(Work.Order_Date, 9, 2) + "/" + ;
>> LEFT(Work.Order_Date, 2) + "/" + ;
>> SUBSTR(Work.Order_Date, 4, 2), ;
>> BatchN.Due_Date WITH ;
>> SUBSTR(Work.Shop_Date, 9, 2) + "/" + ;
>> LEFT(Work.Shop_Date, 2) + "/" + ;
>> SUBSTR(Work.Shop_Date, 4, 2), ;
>> BatchN.SH_Name WITH Work.SH_Name, ;
>> BatchN.SH_Street WITH Work.SH_Street, ;
>> BatchN.SH_City WITH Work.SH_City, ;
>> BatchN.SH_State WITH Work.SH_State, ;
>> BatchN.SH_Phone WITH Work.SH_Phone, ;
>> BatchN.SO_Name WITH Work.SO_Name, ;
>> BatchN.SO_Street WITH Work.SO_Street, ;
>> BatchN.SO_City WITH Work.SO_City, ;
>> BatchN.SO_State WITH Work.SO_State, ;
>> BatchN.SO_Zip WITH Work.SO_Zip, ;
>> BatchN.SO_Phone WITH Work.SO_Phone, ;
>> BatchN.Quantity WITH Work.Quantity, ;
>> BatchN.Part_Num WITH Work.Part_Num, ;
>> BatchN.Desc WITH .GetWholeWords(Work.Desc,1), ;
>> BatchN.Map_Page WITH Work.Map_Page, ;
>> BatchN.Terms WITH Work.Terms, ;
>> BatchN.Ref_Num WITH SUBSTR(Work.Ref_Num, 10, 6)
>>
>> lcDesc=ThisForm.GetWholeWords(Work.Desc, 2)
>> SET STEP ON
>> IF !EMPTY(lcDesc)
>> lnColIndex=lnColIndex + 1
>> cNo = ALLTRIM(STR(lnColIndex))
>> REPLACE ;
>> BatchN.Desc1 WITH lcDesc, ;
>> BatchN.PN1 WITH "", ;
>> BatchN.QTY1 WITH 0
>> lcDesc = ""
>> ENDIF
>> ELSE
>> * Order_Num was found. This means that the first record has
>> * been written to BATCHN.DBF. We are on a subsequent record
>> * from Work.DBF to BacthN.dbf.
>>
>> SET STEP ON
>> IF VAL(Work.Part_Num) > 0
>> lnColIndex=0
>>
>> ELSE
>> lnColIndex=lnColIndex + 1
>> ENDIF
>> cNo = ALLTRIM(STR(lnColIndex))
>>
>> * We have found a numeric part number in the same Order #.
>> * We start over with a new record for the same Order #.
>> IF cNo = "0"
>> APPEND BLANK
>> REPLACE ;
>> BatchN.Line_Item WITH Work.Line_Item, ;
>> BatchN.Order_Num WITH Work.Order_Num, ;
>> BatchN.Order_Date WITH ;
>> SUBSTR(Work.Order_Date, 9, 2) + "/" + ;
>> LEFT(Work.Order_Date, 2) + "/" + ;
>> SUBSTR(Work.Order_Date, 4, 2), ;
>> BatchN.Due_Date WITH ;
>> SUBSTR(Work.Shop_Date, 9, 2) + "/" + ;
>> LEFT(Work.Shop_Date, 2) + "/" + ;
>> SUBSTR(Work.Shop_Date, 4, 2), ;
>> BatchN.SH_Name WITH Work.SH_Name, ;
>> BatchN.SH_Street WITH Work.SH_Street, ;
>> BatchN.SH_City WITH Work.SH_City, ;
>> BatchN.SH_State WITH Work.SH_State, ;
>> BatchN.SH_Phone WITH Work.SH_Phone, ;
>> BatchN.SO_Name WITH Work.SO_Name, ;
>> BatchN.SO_Street WITH Work.SO_Street, ;
>> BatchN.SO_City WITH Work.SO_City, ;
>> BatchN.SO_State WITH Work.SO_State, ;
>> BatchN.SO_Zip WITH Work.SO_Zip, ;
>> BatchN.SO_Phone WITH Work.SO_Phone, ;
>> BatchN.Quantity WITH Work.Quantity, ;
>> BatchN.Part_Num WITH Work.Part_Num, ;
>> BatchN.Desc WITH .GetWholeWords(Work.Desc,1), ;
>> BatchN.Map_Page WITH Work.Map_Page, ;
>> BatchN.Terms WITH Work.Terms, ;
>> BatchN.Ref_Num WITH SUBSTR(Work.Ref_Num, 10, 6)
>>
>> lcDesc=ThisForm.GetWholeWords(Work.Desc, 2)
>> ENDIF
>>
>> * If we know that we are on INDEX 1, meaning DESC1, QTY1, and PN1
>> * and that there is additional WORK.DESC data, lets stay on the
>> * same record in BACTHN and fill in the data for DESC1, QTY1, and PN1.
>>*!* IF cNo = "1"
>>*!* IF !EMPTY(lcDesc)
>>*!*
>>*!*
>>*!* REPLACE ;
>>*!* BatchN.Desc1 WITH lcDesc, ;
>>*!* BatchN.PN1 WITH "", ;
>>*!* BatchN.QTY1 WITH 0
>>*!* lcDesc = ""
>>*!* ENDIF
>>*!* ENDIF
>>
>> * Figure out how to get the Description
>>
>>
>> IF cNo > "1"
>> IF !EMPTY(lcDesc)
>>
>>
>> REPLACE ;
>> BatchN.Desc&cNo WITH lcDesc, ;
>> BatchN.PN&cNo WITH "", ;
>> BatchN.QTY&cNo WITH 0
>> lcDesc = ""
>> ELSE
>> * We are filling in data in columns such as DESC1, QTY1, PN1 or higher.
>> REPLACE ;
>> BatchN.Desc&cNo WITH .GetWholeWords(Work.Desc,1), ;
>> BatchN.PN&cNo WITH Work.Part_Num, ;
>> BatchN.QTY&cNo WITH Work.Quantity
>> * See if there is any additional part of the DESC field remaining.
>> lcDesc=ThisForm.GetWholeWords(Work.Desc, 2)
>> IF !EMPTY(lcDesc)
>> lnColIndex=lnColIndex + 1
>> cNo = ALLTRIM(STR(lnColIndex))
>> REPLACE ;
>> BatchN.Desc&cNo WITH lcDesc, ;
>> BatchN.PN&cNo WITH "", ;
>> BatchN.QTY&cNo WITH 0
>> ENDIF
>> ENDIF
>> ENDIF
>>
>> ENDIF
>> ENDSCAN
>>
____________________________________
Don't Tread on Me
Overthrow the federal government NOW!
____________________________________
