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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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)
 
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.
 
Thanks. It works like charm

John Spencer said:
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)
Frances said:
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.
Click to expand...
Click to expand...
 
Thank you. It works like charm.

Lynn Trapp said:
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://home.bendbroadband.com/conradsystems/accessjunkie.html



Frances said:
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.
Click to expand...
Click to expand...
 
thank you, thank you, thank you. Thanks for writing down the entire SQL.

Jerry Whittle said:
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.


Frances said:
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.
Click to expand...
Click to expand...
 
Back
Top