Conditional removal of a character in a string

G

Guest

Hello
This seems simple I but cannot figure out how to make it work.
I am trying to make a query with 2 columns. 'Record ID' and 'Reference
Record ID'.

Record ID is of a variable length and comes from my Table.
Reference Record ID is the Record ID with a suffix character removed if it
is either a D, P, or Z.

Should look like this.

Record ID Reference Record ID
0012345 0012345
0123456D 0123456
0123456 0123456
12345Z 12345
02CC993P 02CC993

It is probable that some of the IDs will have duplicates once the suffix is
removed and that is fine.

Any help will be appreciated.
 
J

Jeff Boyce

Since the examples you gave are of variable length, and some contain
characters internally, how would you describe to a human assistant how to
know what to do?

Are you saying you ONLY wish to remove a 'trailing' "D", "P", or "Z"?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
G

Guest

Yes correct, the single D, P, and Z characters have meanings and our
customers pasted them on the end. We need to be removed for our use.

It could be possible that a record ID like this occurs "012345EFZZ" where we
would only want the last "Z" removed leaving us "012345EFZ"


Thanks for your quick reply Jeff
 
F

fredg

Hello
This seems simple I but cannot figure out how to make it work.
I am trying to make a query with 2 columns. 'Record ID' and 'Reference
Record ID'.

Record ID is of a variable length and comes from my Table.
Reference Record ID is the Record ID with a suffix character removed if it
is either a D, P, or Z.

Should look like this.

Record ID Reference Record ID
0012345 0012345
0123456D 0123456
0123456 0123456
12345Z 12345
02CC993P 02CC993

It is probable that some of the IDs will have duplicates once the suffix is
removed and that is fine.

Any help will be appreciated.
Click to expand...

IIf(Right([FieldName],1) In ("D","P","Z"),
Left([FieldName],Len([FieldName])-1),[FieldName])
 
G

Guest

Thanks for your reply Fred.
I follow your logic and it looks like it would work, except that where it
should be leaving the suffix-less IDs it seems to just be deleting the entire
ID.
So in the previous list I would end up with just these 2 lines of the
original 5.

0012345
0123456

I need the ID to be included just without the suffix.
This is what I have typed in the criteria in my query.

IIf(Right([RecordID],1) In
("D","P","Z"),Left([RecordID],Len([RecordID])-1),[RecordID])

I am sorry if I am missing something obvious.



fredg said:
Hello
This seems simple I but cannot figure out how to make it work.
I am trying to make a query with 2 columns. 'Record ID' and 'Reference
Record ID'.

Record ID is of a variable length and comes from my Table.
Reference Record ID is the Record ID with a suffix character removed if it
is either a D, P, or Z.

Should look like this.

Record ID Reference Record ID
0012345 0012345
0123456D 0123456
0123456 0123456
12345Z 12345
02CC993P 02CC993

It is probable that some of the IDs will have duplicates once the suffix is
removed and that is fine.

Any help will be appreciated.
Click to expand...

IIf(Right([FieldName],1) In ("D","P","Z"),
Left([FieldName],Len([FieldName])-1),[FieldName])
Click to expand...
 
J

John W. Vinson

Hello
This seems simple I but cannot figure out how to make it work.
I am trying to make a query with 2 columns. 'Record ID' and 'Reference
Record ID'.

Record ID is of a variable length and comes from my Table.
Reference Record ID is the Record ID with a suffix character removed if it
is either a D, P, or Z.

Should look like this.

Record ID Reference Record ID
0012345 0012345
0123456D 0123456
0123456 0123456
12345Z 12345
02CC993P 02CC993

It is probable that some of the IDs will have duplicates once the suffix is
removed and that is fine.

Any help will be appreciated.
Click to expand...

Reference Record ID: IIF(Right([Record ID],1) = "C" OR Right([Record ID], 1) =
"P" OR Right([Record ID], 1) = "Z", Left([Record ID], Len([Record ID]) - 1),
[Record ID])

John W. Vinson [MVP]
 
G

Guest

Thanks for your reply John. I typed in you lines in my query.

However, your answer is similar to fredg's above response in that the 2nd
part of the IIF statement (I assume the part that writes if true) does not
actually return the suffix-less ID in the query. Instead it just skips over
the ID so the end result gives me a list of all the IDs that did not have
have a D, P, or Z suffix.

John W. Vinson said:
Hello
This seems simple I but cannot figure out how to make it work.
I am trying to make a query with 2 columns. 'Record ID' and 'Reference
Record ID'.

Record ID is of a variable length and comes from my Table.
Reference Record ID is the Record ID with a suffix character removed if it
is either a D, P, or Z.

Should look like this.

Record ID Reference Record ID
0012345 0012345
0123456D 0123456
0123456 0123456
12345Z 12345
02CC993P 02CC993

It is probable that some of the IDs will have duplicates once the suffix is
removed and that is fine.

Any help will be appreciated.
Click to expand...

Reference Record ID: IIF(Right([Record ID],1) = "C" OR Right([Record ID], 1) =
"P" OR Right([Record ID], 1) = "Z", Left([Record ID], Len([Record ID]) - 1),
[Record ID])

John W. Vinson [MVP]
Click to expand...
 
G

Guest

Thanks Fred.
It works now.
I was putting the expression in the Criteria instead of the Field.

fredg said:
Hello
This seems simple I but cannot figure out how to make it work.
I am trying to make a query with 2 columns. 'Record ID' and 'Reference
Record ID'.

Record ID is of a variable length and comes from my Table.
Reference Record ID is the Record ID with a suffix character removed if it
is either a D, P, or Z.

Should look like this.

Record ID Reference Record ID
0012345 0012345
0123456D 0123456
0123456 0123456
12345Z 12345
02CC993P 02CC993

It is probable that some of the IDs will have duplicates once the suffix is
removed and that is fine.

Any help will be appreciated.
Click to expand...

IIf(Right([FieldName],1) In ("D","P","Z"),
Left([FieldName],Len([FieldName])-1),[FieldName])
Click to expand...
 
G

Guest

Thanks John.
It works now.
I was putting the expression in the Criteria instead of the Field.

John W. Vinson said:
Hello
This seems simple I but cannot figure out how to make it work.
I am trying to make a query with 2 columns. 'Record ID' and 'Reference
Record ID'.

Record ID is of a variable length and comes from my Table.
Reference Record ID is the Record ID with a suffix character removed if it
is either a D, P, or Z.

Should look like this.

Record ID Reference Record ID
0012345 0012345
0123456D 0123456
0123456 0123456
12345Z 12345
02CC993P 02CC993

It is probable that some of the IDs will have duplicates once the suffix is
removed and that is fine.

Any help will be appreciated.
Click to expand...

Reference Record ID: IIF(Right([Record ID],1) = "C" OR Right([Record ID], 1) =
"P" OR Right([Record ID], 1) = "Z", Left([Record ID], Len([Record ID]) - 1),
[Record ID])

John W. Vinson [MVP]
Click to expand...
 
F

fredg

Thanks for your reply Fred.
I follow your logic and it looks like it would work, except that where it
should be leaving the suffix-less IDs it seems to just be deleting the entire
ID.
So in the previous list I would end up with just these 2 lines of the
original 5.

0012345
0123456

I need the ID to be included just without the suffix.
This is what I have typed in the criteria in my query.

IIf(Right([RecordID],1) In
("D","P","Z"),Left([RecordID],Len([RecordID])-1),[RecordID])

I am sorry if I am missing something obvious.

fredg said:
Hello
This seems simple I but cannot figure out how to make it work.
I am trying to make a query with 2 columns. 'Record ID' and 'Reference
Record ID'.

Record ID is of a variable length and comes from my Table.
Reference Record ID is the Record ID with a suffix character removed if it
is either a D, P, or Z.

Should look like this.

Record ID Reference Record ID
0012345 0012345
0123456D 0123456
0123456 0123456
12345Z 12345
02CC993P 02CC993

It is probable that some of the IDs will have duplicates once the suffix is
removed and that is fine.

Any help will be appreciated.
Click to expand...

IIf(Right([FieldName],1) In ("D","P","Z"),
Left([FieldName],Len([FieldName])-1),[FieldName])
Click to expand...
Click to expand...

Then you must have something else going on.
Here is the result of using your exact expression in the query (even
doubling up the Z's on the last item it will leave one Z as you
requested in one of your other posts).

RecordID Reference Record ID
0012345 0012345 (UNCHANGED)
0123456D 0123456
0123456 0123456 (UNCHANGED)
12345Z 12345
02CC993P 02CC993
012345EFZZ 012345EFZ
0012345X 0012345X (UNCHANGED)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Join based on part of a field 4
Generation of Queries to identify duplicates records in Access Dat 2
LAST of MAX records in query 1
color of field in each record of datasheet depends on other field 1
How do I insert a character in between a string in Access? 1
Find xth occurrence of a specifiecd character in a string 3
Combo Box in Subform Doesn't Accept First Character on a New Recor 1
using dlookup in a query 2

Top