Madam, the following code didn't worked properly, It replaced Newpath with only 'Missing Exchange' while I was trying to get in Newpath as 'C:\JUl-2013\Missing Exchage\123.ps'

Means In my destination path ("c:\JUL-2013") I have created Many Sub folders from the Distinct Master.Exchange and now I am copying files in that each folder. Apart from those folder I have also created a folder "Missing Exchange" and want to keep newpath as 'C:\JUL-2013\Missing Exchage\123.ps' for those files which do not have corrosponding account or exchange code in Master.dbf

I Have my main path as cPath = ADDBS(tDirectory+pBillmonth+'-'+ALLTRIM(STR(pbillyear))).

This code replaced all Newpath with 'Missing exchange', while I needed 'C:\JUL-2013\Missing Exchange\123.ps' in Newpath

* Handle the ones with no match.
UPDATE NewBills ;
  SET MissExg = 0, ;
         NewPath = "Missing Exchange";
  WHERE FileName NOT IN (SELECT AccountNo FROM Master)

Actually, Missing Exchange is the Folder in which I will copy files whose Account Number is not available in the master.dbf

I tried to change the above code as given below, and tried many combinations , but didn't worked.

* Handle the ones with no match.

cPath = ADDBS(tDirectory+pBillmonth+'-'+ALLTRIM(STR(pbillyear)))+'Missing Exchange\'

* Handle the ones with no match.
  UPDATE New_Bills ;
	  SET MissExg = 0, ;
          NewPath = FORCEPATH(FORCEEXT(FileName, "PS"),m.cPath) ;
  WHERE FileName NOT IN (SELECT AccountNo FROM Master)

Although, I have got the solution to my initial problem but again asking you to learn.

Also please tell me , when the variable cPath was used , than how it became m.cpath in next line

Thanks

******************************************************************************************************************








>>Instead, If I try this code it seems to work some faster
>>
>>Update New_bills Set Exchange=(Select Exchange From Master Where Master.Accountno=New_bills.filename) and after getting the name of exchange in My New_bills I will Update Newpath.
>>
>>But the problem using the above command is that I get NULL Values in the field exchange for the filename with are not accountno in Master.dbf, Instead of NULL Values I need 'MISSING EXCHANGE'.
>>
>>I.e for the values for which there is match I need the proper exchange code and for which there is no match I need 'Missing exchange'
>>
>>The SQL code which you provided earlier perhaps dont take E into consideration. E=ALLTRIM(Exchange) in my origininal code.
>>
>
>Yes, I failed to notice that you were actually using the value of E. Not difficult to change the code I gave you, though:
>
>>cPath = ADDBS(tDirectory+pBillmonth+'-'+ALLTRIM(STR(pbillyear)))
>
>

>* Handle the ones that match first.
>UPDATE NewBills ;
>  SET MissExg = 1, ;
>          NewPath = FORCEPATH(FORCEEXT(FileName, "PS")+"\" + ALLTRIM(Master.Exchange), m.cPath) ;
>  FROM Master ;
>  WHERE NewBills.FileName = Master.AccountNo
>
>* Handle the ones with no match.
>UPDATE NewBills ;
>  SET MissExg = 0, ;
>          NewPath = "Missing Exchange";
>  WHERE FileName NOT IN (SELECT AccountNo FROM Master)
>

>
>Tamar