disregard leading zero in query

If myfieldPK contains always only numbers you can do:

...ON table1.MyField_PK = trim(str(table2.MyField_PK ))

would this work  :  Mid([field],1) in where clause ?

@fordraiders that will create problems for the cases where the field does not contain a leading ZERO

ON Val(table1.MyField_PK) = table2.MyField_PK

Noting Val(088881221) = 88881221

Doing a join on a calculated column will result in the query engine not being able to use any index to optimize the join.  A full table scan would always be required.

Why not fix the problem by not stripping the 0 to begin with.  Text strings that are all numbers frequently include leading zeros.  True numbers do not.  Never convert numeric strings to number data types.  Always leave them as strings.

You can convert the short field to text and then run an update query to append the leading zero.

Update YourTable Set SomeField = Format(SomeField, "000000000");

Use as many zeros as you need to get to the length of the text field.  From your example, it looks like the text field should be 9 characters - so 9 zeros.  123 will end up as 000000123.  123456789 will not change.

Change the fields from string to number.

or

Add a column to the table(s) to contain the numeric value of the text column

thanks...to all