Custom Format - Join

[CraigH]

I have a query that I would like to join fields, but the
fields are formatted differently. See example -

Table1 Table2
1234567-4 1234567-0004
1234567-24 1234567-0024
M485743895A M485743895A

Is there a way to change the format of the field for
table1 so that each of the entries would change as shown,
but not impact the third entry?

 

[John Spencer (MVP)]

You will have to use a non-equi join. This cannot be done in the query grid but
most be done using the SQL text window. That might look something like the
untested code below

SELECT *
FROM TABLE1 INNER JOIN TABLE2
ON IIF(Table1.FIELDA Like "*-*",
Left(Table1.FieldA,Instr(1,Table1.FieldA,"-")) & Format(Val(Mid(Table1.FieldA,Instr(1,Table1.FieldA,"-")+1)),"0000"),
Table1.FieldA) = Table2.FieldB

The IIF statement checks to see if there is a dash in the field, if there is it
breaks fieldA into two parts. Part One is all of fieldA up to and including the
dash. Part two is all the remainder, formatted to 4 numeric characters with
leading zeros

If no dash, then it just returns fieldA.


You could also use the following which you can build in the query grid. (No
Join line between the two tables and the complex IIF expression in the criteria
under the appropriate field.

SELECT *
FROM TABLE1 , TABLE2
WHERE Table2.FIELDB =
IIF(Table1.FIELDA Like "*-*",
Left(Table1.FieldA,Instr(1,Table1.FieldA,"-")) & Format(Val(Mid(Table1.FieldA,Instr(1,Table1.FieldA,"-")+1)),"0000"),
Table1.FieldA)