Would like query to get data from one table into the query from another table based on matching a word within a string. There are a few records where the functions I used didn't quite get there and wondering how might be able to get the stragglers.
In this example containing the relevant fields from each table/query trying to match tblData.Location to tblLocation.Location on <Hendy> since that keyword is common to both fields in order to get the Segment Code which can then derive the Segment from tblSegment <Segment 3: MP 28.98 to MP 43.80>
End Result / what is needed:
qrySegmentLocation
Since I couldn't join the tables I used dlookup after doing some string manipulation. This is what I used and it got about 80% of the data.
In this example containing the relevant fields from each table/query trying to match tblData.Location to tblLocation.Location on <Hendy> since that keyword is common to both fields in order to get the Segment Code which can then derive the Segment from tblSegment <Segment 3: MP 28.98 to MP 43.80>
End Result / what is needed:
qrySegmentLocation
Segment | Filename | |
Segment 3: MP 28.98 to MP 43.80 | Report XYZ 20231026.pdf |
tblData | ||
Location | Filename | |
MTN View/Hendy Set out Track | Report XYZ 20231026.pdf |
tblLocation | ||
SegmentCode | Location | |
S3 | WPC 17 at CP Hendy |
tblSegment | ||
SegmentCode | Segment | |
S3 | Segment 3: MP 28.98 to MP 43.80 |
Since I couldn't join the tables I used dlookup after doing some string manipulation. This is what I used and it got about 80% of the data.
Code:
IIf(IsNull([location]),[Inspection255],[Location]) AS LocationInspec,
IIf([location] Is Null,Mid(regexpatternextract([Inspection255],"MP\s\d{1,2}\."),4,3),IIf(Left([Location],3)="MP ",Mid([Location],4,3),[Location])) AS ParseLoc1,
Replace(Replace([ParseLoc1]," Station","")," Crossing","") AS ParseLoc2,
DLookUp("Segment","qrySegmentLocations","SegmentLocation Like '*" & [ParseLoc2] & "*'") AS Segment,