Format text to add character

[Mary]

I have two fields that should match, but one includes special characters
while the other does not.

Example:
Field1 00ABCD123456123
Filed2 00/ABCD/123456/123/SBZ

I need to find records where these two fields don't match, either by
changing the display of one of them, or a query to compare Field1 character
7-15 with Field2 characters 9-14, 16-18.

Hope this makes sense.

Can anyone help?

Thanks!

 

[Ron]

Take a look at the following from the Access Help file it might be what
you're looking for...

Extract a part of a text value

The following table lists examples of expressions that return a part of a
string.

You can use these expressions in calculated controls (calculated control: A
control that is used on a form, report, or data access page to display the
result of an expression. The result is recalculated each time there is a
change in any of the values on which the expression is based.) on forms,
reports, and data access pages (data access page: A Web page, published from
Access, that has a connection to a database. In a data access page, you can
view, add to, edit, and manipulate the data stored in the database. A page
can also include data from other sources, such as Excel.).

Expression Description
=Left([ProductName], 1) Displays the first character of the value of the
ProductName field.

=Right([AssetCode], 2) Displays the last 2 characters of the value of the
AssetCode field.

=Mid([MyString], 18, 4) Displays "Emma," if MyString contains the value
"Congratulations, Emma."


You can use these expressions in a calculated field in a query.

Expression Description

ProductInitial: Left([ProductName], 1) Displays in the ProductInitial field
the first character of the value in the ProductName field.

TypeCode: Right([AssetCode], 2) Displays in the TypeCode field the last two
characters of the value in the AssetCode field.

AreaCode: Mid([Phone], 2, 3) Displays in the AreaCode field the three
characters starting with the second character of the value in the Phone
field.

 

[John W. Vinson]

I have two fields that should match, but one includes special characters
while the other does not.

Example:
Field1 00ABCD123456123
Filed2 00/ABCD/123456/123/SBZ

I need to find records where these two fields don't match, either by
changing the display of one of them, or a query to compare Field1 character
7-15 with Field2 characters 9-14, 16-18.

What's the context? You can compare

Mid([Field1], 7, 9)

with

Mid([Field2], 9, 6) & Mid([Field2], 16, 3)

but I'm not sure what you intend to do with the comparison!

 

[Mary]

the fields should match, but some don't. I need to find the ones that don't
so I can follow up on them.

I have two fields that should match, but one includes special characters
while the other does not.

Example:
Field1 00ABCD123456123
Filed2 00/ABCD/123456/123/SBZ

I need to find records where these two fields don't match, either by
changing the display of one of them, or a query to compare Field1 character
7-15 with Field2 characters 9-14, 16-18.

What's the context? You can compare

Mid([Field1], 7, 9)

with

Mid([Field2], 9, 6) & Mid([Field2], 16, 3)

but I'm not sure what you intend to do with the comparison!

 

[John Spencer]

To find a match

WHERE Field2 LIKE Format([Field1],"@@\/@@@@\/@@@@@@\/@@@") & "*"

To find a non match
WHERE Field2 NOT LIKE Format([Field1],"@@\/@@@@\/@@@@@@\/@@@") & "*"

Or
WHERE Mid(Field2,9) Like MID(Format(Field1,"@@\/@@@@\/@@@@@@\/@@@"),9) & "*"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

Use the calculated expressions a criterion with <> as the operator: the SQL
would be something like

SELECT * FROM yourtable

the fields should match, but some don't. I need to find the ones that don't
so I can follow up on them.

I have two fields that should match, but one includes special characters
while the other does not.

Example:
Field1 00ABCD123456123
Filed2 00/ABCD/123456/123/SBZ

I need to find records where these two fields don't match, either by
changing the display of one of them, or a query to compare Field1 character
7-15 with Field2 characters 9-14, 16-18.

What's the context? You can compare

Mid([Field1], 7, 9)

with

Mid([Field2], 9, 6) & Mid([Field2], 16, 3)

but I'm not sure what you intend to do with the comparison!