Thank you! Thank you! Thank you! Your suggestion worked beautifully Tim! ..and if I wasn't clear before...THANK YOU!! :)
-----Original Message----- From: Raster, Tim [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 1:57 PM To: SQL Subject: RE: odd problem OK, I've got bad news, and.... well, that's all I have. I think what's happening is that by putting the "+' %'" stuff on it, it's looking for a string that actually contains the % character, not using it as a wildcard. (you can test this by putting " %" characters on the end of Rose's first name and see if pulls it up then) I think instead, try doing something like this: ... OR ( (left(trim(Total_Employee_List.ISP_User_First_Name),len(trim(Total_Emplo yee_List.ISP_User_First_Name)))) LIKE left(trim(Valid_Employees.First_Name),len(trim(Total_Employee_List.ISP_U ser_First_Name))) AND trim(Total_Employee_List.ISP_User_Last_Name) = trim(Valid_Employees.Last_Name) ) OR ... You'll need like 4 of these, since you'll have to switch them around each possible way, and watch out for which is longer, the len() of Total_Employee_List's field or Valid_Employees's field. If you use a min() for the len(), that might work, otherwise you'll have to use the 2 X 2 combinations. Gee, reading that back to myself, it's so clear... :P Let me try it in English... instead of comparing strings that start the same and end with anything (the % wildcard) search for strings whose left-most N characters are the same. To determine N, either use a constant (i.e. like 10), or use the len() of one of the strings. Do it both ways, so that "John" and "Johnathan" match, as well as "Johnathan" and "John" match the other way around. Still not clear, is it? :( -----Original Message----- From: Conaway, Amy C [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 12:15 To: SQL Subject: RE: odd problem Here is the whole query. I had it set up earlier such that it only had the first two conditions: match the badge or name (w/o MI)...that worked fine. Now, even though I added the other two conditions, the query still comes up with the same number of records. The record I'm checking for has the incorrect badge number (so it won't show up based on that) and the name is "Rose A" (first name) "Riggins" (last name). There is a "Rose" (first name) "Riggins" (last name) in the other table. Why doesn't the query pick up the match? SELECT Vendor_Circuit_Number, Total_Employee_List.ISP_User_Last_Name, Total_Employee_List.ISP_User_First_Name, Valid_Employees.Last_Name, Valid_Employees.First_Name FROM Total_Employee_List INNER JOIN Valid_Employees ON (Total_Employee_List.ISP_Employee_Badge_Number=Valid_Employees.[Badge Number]) Or (trim(Total_Employee_List.ISP_User_First_Name)+trim(Total_Employee_List. ISP_User_Last_Name)=trim(Valid_Employees.First_Name)+trim(Valid_Employee s.Last_Name)) OR ( (trim(Total_Employee_List.ISP_User_First_Name)+" %") LIKE trim(Valid_Employees.First_Name) AND trim(Total_Employee_List.ISP_User_Last_Name) = trim(Valid_Employees.Last_Name) ) OR ( trim(Total_Employee_List.ISP_User_First_Name) LIKE (trim(Valid_Employees.First_Name)+" %") AND trim(Total_Employee_List.ISP_User_Last_Name) = trim(Valid_Employees.Last_Name) ) ; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:6 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:> Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
