"Shaun" <no****@nospam.com> wrote in message
news:YL*******************@stones.force9.net...
Hi,
I have a table of Locations around the country. My system produces reports
based on these Locations. I also have a table containing Brick Codes e.g.
Brick Post Code
AB51 AB51
AB52 AB52
AB55 AB55
AB56 AB56
AL01 AL1
AL02 AL2
AL03 AL3
AL04 AL4
How can I compare the first 3 or 4 letters of the postcode in the
Locations table to the corresponding entry in the Brick Codes table so I can add it
to my report?
Use the substring function to get the n letters and the case
function to compare, such as ...
case when substring(brick,1,2) = substring([post code],1,2) then 'matching
1st 2' else 'no match' end "Comparison 1 of x",
case when substring(brick,1,3) = substring([post code],1,3) then 'matching
1st 3' else 'no match' end "Comparison 2 of x",
case when substring(brick,1,4) = substring([post code],1,4) then 'matching
1st 4' else 'no match' end "Comparison 3 of x",
etc