Linking 2 Fields, How to Omit First two Digits during Link

[KrenzyRyan]

I am trying to link up two fields that are close, but not completely identical.

Here are the two fields:



Field 1:

Order

5004645554

Field 2:

ORDER_NUM

005004645554


There are two zeros before each of the order numbers in the other field...how to I properly link these two?

[June7]

You cannot join on this data, not directly. You can do a query of one of the tables, include a constructed field that formats the value as needed. Then join the query with the other table using this constructed field.

[pbaldy]

One way is to create a query on one table that creates a new field in the appropriate format. In subsequent queries, join on that query instead of the original table.

[pbaldy]

FYI, you can join the tables directly with this type of thing:

SELECT...
FROM OneTable INNER JOIN OtherTable ON Mid(OneTable.FieldName, 3) = OtherTable.FieldName

or the reverse, using the format function to pad zeros. The problem is that it the query design grid can't represent it, so you have to work in SQL view from then on.

[KrenzyRyan]

Can you give me an example of a "consructed field"? Or maybe how to create one?

[pbaldy]

In SQL view:

SELECT FieldName, Mid(FieldName, 3) AS ConstructedFieldName
FROM TableName

In design view

ConstructedFieldName: Mid(FieldName, 3)

[June7]

Construct a field with an expression in the query designer grid. Access Help has more guidance.

As pbaldy pointed out, can join the tables if build the query in SQL View window of the Query Designer by typing the statement. Can use the Query Designer to build a SELECT query joining on the two fields and retrieving desired fields (or wildcard for all) then in the SQL View modify as needed. After modifying for this structure, won't be able to view in the Design View.

[KrenzyRyan]

This worked great, thanks guys!