poll records if the first three characters matches in two data fie

poll records if the first three characters matches in two data fie

Post by RnJhbmNlc » Sat, 29 Apr 2006 04:10:01


I need to poll records from two tables if
left([table1].[field1], 3)=left([table2].[field3],3).
I used the above line in critiria but returned 0 record. Please help.
Thanks.
 
 
 

poll records if the first three characters matches in two data fie

Post by Lynn Trap » Sat, 29 Apr 2006 04:26:55

You'll have to modify this to suit your needs, but this should get you
started.

Select Table1.*, Table2.*
From Table1 INNER JOIN Table2 ON Left(Table2.Field3,3) =
Left(Table1.Field1,3);

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://www.yqcomputer.com/

 
 
 

poll records if the first three characters matches in two data fie

Post by John Spenc » Sat, 29 Apr 2006 04:34:36

Are the records joined on the two fields? If so, unless they exactly match,
the join has already eliminated the record before the where criteria is
applied.

You can remove the join and use the criteria or you can switch to the sql
view and replace the
ON [table1].[field1] = ([table2].[field3])
with
ON left([table1].[field1], 3) = left([table2].[field3],3)
 
 
 

poll records if the first three characters matches in two data fie

Post by SmVycnkgV2 » Sat, 29 Apr 2006 04:42:01

SELECT [Table1].[Field1]
FROM [Table1]
WHERE Left([Table1].[Field1],3) IN
(SELECT Left([ Table2].[FIELD3],3) AS Expr1
FROM [Table2])
UNION ALL
SELECT [Table2].[Field3]
FROM [Table2]
WHERE Left([Table2].[Field3],3) IN
(SELECT Left([Table1].[Field1],3) AS Expr1
FROM [Table1]);

This would grab all the records from both tables that matched.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.