Trim Fields in Access 2000

[larper]

I have two tables, both containing tracking #. One is from FedEx and the other is generated from SQL. I need to match up the Tracking # from each table, however both of the tables fields have different preceding identifiers before the Tracking #, which are 14 charters long. I know in Excel I would just Right(14) and have matching data. But what is the statement I can use in Access?

Thanks for all help!

 

[Norie]

Well it's basically the same.

It just depends exactly what you want to do.

Do you want to replace the existing tracking #'s with the 14 character long one?

If so you would need to run an update query on both tables.

 

[larper]

I believe I rather do this in a query, since I have some of the tables linked.

Larry

 

[Norie]

Larry

What do you mean?

 

[mdmilner]

I think I get it - he wants to join the two tables, but, he has to join on a portion of the string inside a field.

How about as a baseline

SELECT tbl1.fld1, tbl1.fld2, tbl2.field1, tbl2.field2 FROM tbl2
INNER JOIN tbl1 ON tbl2.field1 = tbl1.fld1;

And, modified for partial strings within fields

Select Right(tbl1.fld1,len(tbl1.fld1)-14) As key1, tbl1.fld2, Right(tbl2.field1, len(tbl2.field2)-14) as key2, tbl2.field2 INNER JOIN tbl1 ON key2 = key1;

Ok syntax is off - I got it to work when I
1) built a query that creates a "field" with removes the unneeded 14 chars
2) built a join query on those two queries

Below is actually my live query example from a test database.
But, adapt this by setting the key1 & key2 as just above.

SELECT insp1.FILENUM AS key1, insp1.YY1, insp1.MM1, insp1.DD, insp1.INPUTBY, insp1.reason, insp1.pc1, insp1.pc2, insp1.pc3, insp1.dc1, insp1.dc2, insp1.dc3, insp1.SHIPR, insp1.ORIGRD
FROM insp1;

SELECT insp2.ORIGCIT, insp2.ORIGST, insp2.CONSIG, insp2.DESTRD, insp2.DESTCIT, insp2.DESTST, insp2.WAYBILL, insp2.WBDATE, insp2.EINIT, insp2.ENUM, insp2.STCC1, insp2.CNT, insp2.FILENUM2 AS key2
FROM insp2;

SELECT insp1q.*, [insp2q].[ORIGCIT], [insp2q].[ORIGST], [insp2q].[CONSIG], [insp2q].[DESTRD], [insp2q].[DESTCIT], [insp2q].[DESTST], [insp2q].[WAYBILL], [insp2q].[WBDATE], [insp2q].[EINIT], [insp2q].[ENUM], [insp2q].[STCC1], [insp2q].[CNT]
FROM insp1q INNER JOIN insp2q ON [insp1q].[key1]=[insp2q].[key2];

Mike

 

[Norie]

Mike

That's what I'm thinking too and I've done similar things in the past.

I know it's possible but would need to be done using the SQL view as the join would not be able to be displayed in the QBE.

Also, as far as I remember, it's a bit resource intensive and slow.

If I was doing it in a real situation I would probably create a new field, say Tracking14, and populate that with an update query using

Right([TrackingNo],14)

That way you keep the original field intact.

 

[larper]

Right([TrackingNo],14), seems what I want to do, however I'm getting
a Msg Box to enter Parameter Value, for the Expr field I created.

 

[mdmilner]

Did you:

1) build a query for each separate table that defines the new "field"
2) Set that field to some name?

fldName1: Right([TrackingNo],14)

Which is equivalent in SQL to:

Right([TrackingNo],14) As fldName1

And the:

3) Build a third query based on the prior two queries and use fldName1 as your field name?

Mike

 

[Norie]

larper

Did you change the field name to match yours?