How to Concatenate in MS Access

I have (2) different issues related to concatenate. In one instance, I want
to trim the last character of a string and compare it to a field in another
table. And in the other instance, I want to combine two fields so that I can
compare that new field with a field in another table.

1: I have a field with the value 123456S in one table. And a field with
123456 in another table. How do I remove the S from the field in the first
table and then compare it to the field in the second table?

2: I have a table with a field with the following data: GR12345-2. In
another table, there are two fields which create this value: field 1:
GR12345 and field 2: 2. How do I combine field 1 and 2 in the second table
to compare it to the field in the 1st table?

Queries...

Create a query that removes the "S". Use THAT query to join with a second
table.

Create a query that concatenates the fields (with appropriate symbols). Use
THAT query to join with you other second table.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"ttp" wrote in message
...
I have (2) different issues related to concatenate. In one instance, I
want
to trim the last character of a string and compare it to a field in
another
table. And in the other instance, I want to combine two fields so that I
can
compare that new field with a field in another table.

1: I have a field with the value 123456S in one table. And a field with
123456 in another table. How do I remove the S from the field in the
first
table and then compare it to the field in the second table?

2: I have a table with a field with the following data: GR12345-2. In
another table, there are two fields which create this value: field 1:
GR12345 and field 2: 2. How do I combine field 1 and 2 in the second
table
to compare it to the field in the 1st table?

1. To remove the last character of a string, you can use

Left([NameOfField], Len([NameOfField]) - 1)

2. [Field1] & "-" & [Field2]


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ttp" wrote in message
...
I have (2) different issues related to concatenate. In one instance, I
want
to trim the last character of a string and compare it to a field in
another
table. And in the other instance, I want to combine two fields so that I
can
compare that new field with a field in another table.

1: I have a field with the value 123456S in one table. And a field with
123456 in another table. How do I remove the S from the field in the
first
table and then compare it to the field in the second table?

2: I have a table with a field with the following data: GR12345-2. In
another table, there are two fields which create this value: field 1:
GR12345 and field 2: 2. How do I combine field 1 and 2 in the second
table
to compare it to the field in the 1st table?

It would be immensely helpful to those trying to help you if you would
_define_ the values you want to manipulate, instead of giving an unexplained
example. For instance, where you show "123456S", that could be a numeric
with a concluding alpha-numeric and you want to strip the last character,
but it could also just be mixed numeric and alphanumerics from which you
want to remove all alphanumeric characters, or it could be something else
again. Each would require a different approach.

For other good suggestions on effective use of newsgroups, see the FAQ at
http://www.mvps.org/access/netiquette.htm.

Larry Linson
Microsoft Office Access MVP
"ttp" wrote in message
...
I have (2) different issues related to concatenate. In one instance, I
want
to trim the last character of a string and compare it to a field in
another
table. And in the other instance, I want to combine two fields so that I
can
compare that new field with a field in another table.

1: I have a field with the value 123456S in one table. And a field with
123456 in another table. How do I remove the S from the field in the
first
table and then compare it to the field in the second table?

2: I have a table with a field with the following data: GR12345-2. In
another table, there are two fields which create this value: field 1:
GR12345 and field 2: 2. How do I combine field 1 and 2 in the second
table
to compare it to the field in the 1st table?