VBA, Inner Join Question

Inner Join Question

Author	Message
Stev #1 / 4	Inner Join Question Is it possible to to do an inner join from a field in one table to part of a field in another table. For example: Select * From table1 Inner Join table2 On table1.Field1 = Right(table2.Field1,4); I've been trying this after giving up on a Dlookup function to find the field of a value for running an append query. For example: SQLtxt = "INSERT INTO table1 ( lngSiteID, intYear)" & _ " SELECT DLookUp('[lngIDSite]','tblMonitorSites','Right ([lngAIRScode],4) = table2.[Field5]') AS Expr1, Field11" & _ " FROM table2;" DoCmd.RunSQL SQLtxt I cannot get the table2.[Field5] reference in the Dlookup statement to work properly. Any help with either of these is appreciated.
Sun, 03 Oct 2004 04:48:06 GMT

Calvin Smit #2 / 4	Inner Join Question Yes, the way I've always done that, was to put another query in the middle that parsed my column first. Then relate that query and my main table. -- Calvin Smith http://www.CalvinSmithSoftware.com "Real-world Source Code Solutions" * http://www.CalvinSmithSoftware.com/CalvinSmithDoesWebsites.htm * Quote: > Is it possible to to do an inner join from a field in one > table to part of a field in another table. For example: > Select * From table1 Inner Join table2 On table1.Field1 = > Right(table2.Field1,4); > I've been trying this after giving up on a Dlookup > function to find the field of a value for running an > append query. For example: > SQLtxt = "INSERT INTO table1 ( lngSiteID, intYear)" & _ > " SELECT DLookUp('[lngIDSite]','tblMonitorSites','Right > ([lngAIRScode],4) = table2.[Field5]') AS Expr1, Field11" & > _ " FROM table2;" > DoCmd.RunSQL SQLtxt > I cannot get the table2.[Field5] reference in the Dlookup > statement to work properly. > Any help with either of these is appreciated.
Sun, 03 Oct 2004 05:14:57 GMT

Stev #3 / 4	Inner Join Question Solved the inner join problem. I was having problems with variable types. It's alway something stupid. Still mystified over the Dlookup problem though.
Sun, 03 Oct 2004 05:53:26 GMT

david #4 / 4	Inner Join Question You had table2.field5 inside a string literal. - you wanted ...." & [table2.field5 & "'......... (david) Quote: > Solved the inner join problem. I was having problems with > variable types. It's alway something stupid. Still > mystified over the Dlookup problem though.
Sun, 03 Oct 2004 15:52:50 GMT

Page 1 of 1

[ 4 post ]