Trim Fields in Access 2000

larper

Board Regular
Joined
Jan 13, 2005
Messages
66
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! :oops:
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 13, 2005
Messages
66
I believe I rather do this in a query, since I have some of the tables linked.

Larry
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Larry

What do you mean?
 

mdmilner

Well-known Member
Joined
Apr 30, 2003
Messages
1,352

ADVERTISEMENT

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

Code:
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

Code:
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.

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 13, 2005
Messages
66

ADVERTISEMENT

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

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
larper

Did you change the field name to match yours?
 

Forum statistics

Threads
1,148,270
Messages
5,745,787
Members
423,973
Latest member
man_this_is_hard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top