Help with select statement

[emboughey]

I'm hoping this is a quick fix. I couldn't find anything but maybe I wasn't looking for the right syntax.

I need to use only the first 3 characters of facility_id but I'm not sure how to do it. Can you guys help?


sql = "SELECT Planet_Code,Job_Number, Job_Name, format(Mail_Date,'00/00/0000')as Mail_Date , Qty_Mailed, facility_id as zip, date_time as [scan date], Op_code as opcodes,count(*) as [qty scanned] FROM " & USPS_FILE & Chr(44) & EMD_FILE & " WHERE [planet] = left([Planet_Code],11) AND " & SearchCriteria & " GROUP BY Planet_Code,Job_Number, Job_Name, Mail_Date , Qty_Mailed, facility_id, date_time, Op_code order by Planet_Code, date_time

 

[ptheriault]

use the substring function.

SUBSTRING ( expression ,start , length )

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[bborissov]

sql = "SELECT Planet_Code,Job_Number, Job_Name, format(Mail_Date,'00/00/0000')as Mail_Date , Qty_Mailed, LEFT(facility_id,3) as zip, date_time as [scan date], Op_code as opcodes,count(*) as [qty scanned] FROM " & USPS_FILE & Chr(44) & EMD_FILE & " WHERE [planet] = left([Planet_Code],11) AND " & SearchCriteria & " GROUP BY Planet_Code,Job_Number, Job_Name, Mail_Date , Qty_Mailed, LEFT(facility_id,3), date_time, Op_code order by Planet_Code, date_time"

That will work if facility_id has some kind of CHAR type (varchar, Nvarchar, char or NChar). If it is other type you should convert it first.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[emboughey]

It worked fine this time. I didn't have both of them set up for 3 characters.. Thanks.

One more thing. I need to add something else I need it to look for Op_Code > "897" but it's not working. I believe it is a character field which is why I put the quotes around it.

WHERE [planet] = left([Planet_Code],11) AND Op_code > '897' AND " & SearchCriteria & "

Thanks in advance for any assistance

 

[CaptainD]

My guess is you need to convert or cast it to a numeric in order to look for something > then