An index order has no affect on queries because they don't use already open tables.
You may find following articles helpful
- http://www.berezniker.com/content/pages/visual-foxpro/queries-and-functions-which-accept-alias-parameter
- http://www.berezniker.com/content/pages/visual-foxpro/queries-and-filtered-cursors
- http://www.berezniker.com/content/pages/visual-foxpro/string-comparison-vfp

It's a bad idea to use one letter meaningless table aliases in a query. It makes the query unreadable. It also could be dangerous because letters A-J are reserved for work areas 1-10.
Your query takes long because it's not optimized. Make sure that you have indexes clmhderr.seq_no and clmhderr.error2 and following query will be optimized. You can use SYS(3054) to check a query optimization.

SELECT hd.provid,hd.provname, hd.provad1, hd.provad2, hd.prov_city, hd.prov_state,hd.prov_zip,;
		hd.npi, hd.membid,hd.membname, hd.mem_ad1, hd.mem_ad2, hd.mem_city, hd.mem_st, hd.mem_zip,;
		hd.mem_dob,hd.diag1, hd.diag2,hd.diag3,hd.diag4, hd.PLACESVC, hd.NBRUNITS, hd.refprov_nm,;
		hd.prov_taxid, hd.e_fname,;
		dt.begdt,dt.enddt,dt.svc_code,dt.MODIF,dt.qty, dt.anthtime,dt.billed, dt.DIAGREF;
	FROM clmhderr hd,clmdterr dt;
	WHERE hd.seq_no = dt.seq_no AND hd.seq_no LIKE  mmyy + "%" AND hd.error2 LIKE "E%";
	INTO CURSOR c_tmp

REPO FORM clmhderr_emp TO PRINT PROMPT

>Below is the SELECT Statement, I open and set the order on the two tables just before i execute the select

>SELECT a.provid,a.provname, a.provad1, a.provad2, a.prov_city, a.prov_state,a.prov_zip,;
>		a.npi, a.membid,a.membname, a.mem_ad1, a.mem_ad2, a.mem_city, a.mem_st, a.mem_zip,;
>		a.mem_dob,a.diag1, a.diag2,a.diag3,a.diag4, a.PLACESVC, a.NBRUNITS, a.refprov_nm,;
>		a.prov_taxid, a.e_fname,;
>		b.begdt,b.enddt,b.svc_code,b.MODIF,b.qty, b.anthtime,b.billed, b.DIAGREF;
>	FROM clmhderr a,clmdterr b;
>	WHERE a.seq_no = b.seq_no AND SUBSTR(a.seq_no,1,8) = mmyy AND SUBSTR(a.error2,1,1) = 'E';
>	INTO CURSOR c_tmp
>
>REPO FORM clmhderr_emp TO PRINT PROMPT