Update Query

[Sarah]

I have data from table 1 that I am trying to join with table 2. Some
(not all) of the data from table 1 has a suffix ("SR" or "TR") at the
end of it thats not allowing it to join correctly.

How do I get rid of the "SR" and "TR"?

Example..

Table 1 has information that looks like this: KL42SR or KL42TR
I want my data to look like this: KL42

Not all data has this ending, and so you cant just replace the last
two characters with " ".

Is there a way to write a Replace statement for both?

Replace([Table 1]!Grade,"SR","") did not work.

 

[KARL DEWEY]

Create a select query from each table and add a calculated field like this --
NewJoinField: Replace(Replace([Table 1].[Grade],"SR",""), "TR","")
Use this field to join the queries.

 

[John W. Vinson]

I have data from table 1 that I am trying to join with table 2. Some
(not all) of the data from table 1 has a suffix ("SR" or "TR") at the
end of it thats not allowing it to join correctly.

How do I get rid of the "SR" and "TR"?

Example..

Table 1 has information that looks like this: KL42SR or KL42TR
I want my data to look like this: KL42

Not all data has this ending, and so you cant just replace the last
two characters with " ".

Is there a way to write a Replace statement for both?

Replace([Table 1]!Grade,"SR","") did not work.

If you want to permanently and irrevokably remove the suffix from Table1
(which you may or may not want to do!) you can run an Update query.

Make and test a backup of your database FIRST!

Then, select the table in a new Query. Put a criterion on the field of

LIKE "*[TS]R"

Open the query in datasheet view to verify that it is retrieving only those
records where the field ends in TR or SR.

If it's ok, then change the query to an Update query and update the field to

Left([fieldname], Len([fieldname]) - 2)

Run the query and you should have expunged the suffix forever.

 

[Sarah]

I have data from table 1 that I am trying to join with table 2. Some
(not all) of the data from table 1 has a suffix ("SR" or "TR") at the
end of it thats not allowing it to join correctly.

How do I get rid of the "SR" and "TR"?

Table 1 has information that looks like this: KL42SR or KL42TR
I want my data to look like this: KL42

Not all data has this ending, and so you cant just replace the last
two characters with " ".

Is there a way to write a Replace statement for both?

Replace([Table 1]!Grade,"SR","") did not work.

If you want to permanently and irrevokably remove the suffix from Table1
(which you may or may not want to do!) you can run an Update query.

Make and test a backup of your database FIRST!

Then, select the table in a new Query. Put a criterion on the field of

LIKE "*[TS]R"

Open the query in datasheet view to verify that it is retrieving only those
records where the field ends in TR or SR.

If it's ok, then change the query to an Update query and update the fieldto

Left([fieldname], Len([fieldname]) - 2)

Run the query and you should have expunged the suffix forever.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

 

[Sarah]

I have data from table 1 that I am trying to join with table 2. Some
(not all) of the data from table 1 has a suffix ("SR" or "TR") at the
end of it thats not allowing it to join correctly.

How do I get rid of the "SR" and "TR"?

Table 1 has information that looks like this: KL42SR or KL42TR
I want my data to look like this: KL42

Not all data has this ending, and so you cant just replace the last
two characters with " ".

Is there a way to write a Replace statement for both?

Replace([Table 1]!Grade,"SR","") did not work.

If you want to permanently and irrevokably remove the suffix from Table1
(which you may or may not want to do!) you can run an Update query.

Make and test a backup of your database FIRST!

Then, select the table in a new Query. Put a criterion on the field of

LIKE "*[TS]R"

Open the query in datasheet view to verify that it is retrieving only those
records where the field ends in TR or SR.

If it's ok, then change the query to an Update query and update the fieldto

Left([fieldname], Len([fieldname]) - 2)

Run the query and you should have expunged the suffix forever.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

When I put LIKE"*[TS]R" in the Criteria field it did only grab the
fields with "TR" and "SR".

However, when I changed it to an update query and put LEFT([Grade],Len
([Grade])-2) it didnt take off the "TR" and "SR"

 

[John W. Vinson]

However, when I changed it to an update query and put LEFT([Grade],Len
([Grade])-2) it didnt take off the "TR" and "SR"

Where did you put the Left() call? And did you *run* the query, by clicking
the ! icon in the toolbar, or did you just open the query datasheet (which
will just show the records prior to running the update)?

I was suggesting that you put it in the Update To line, where it should work.
Perhaps you could post the SQL view of the query if it's still not working.