Delete Last Character From String 1

[lss]

This should be a simple task, but I can't find the right coding that will make this work. I've tried combining expressions using Left, Right, Mid, InStr, Replace.

In an Access Query we have 5 separate fields. Data is always in Field1. Field2 to Field5 may contain data or may be blank, but the data always exists in sequence. In other words, you wouldn't have a blank Field2 and data in Field3.

Examples:
Field1
Field1 Field2 Field3
Field1 Field2
Field1 Field2 Field3 Field4 Field5

We're creating a new field to list the 5 fields of each record. We need to separate each field with a semicolon, except, of course, for the last one that appears in the list. To begin, we determine whether the field is null, and if not, output the fieldname and a semicolon for each.

The combined list shows:
Field1;
Field1; Field2; Field3;
Field1; Field2;
Field1; Field2; Field3; Field4; Field5;

All I need to do now is remove the last semicolon. I can extract just the semicolon using:

ShowSem: Right(Trim([CombinedFieldsList]),1)

But I need to show the results LESS the semicolon. Any tips are appreciated! Thank you

 

[lupins46]

myresult:Left(CombinedFieldsList, len(Combinedfieldslist)-1)

 

[lss]

Thank you! I had to include Trim([CombinedFieldsList]), must be a space at the end somehow (I do have Trim defined on the CombinedFieldsList too), but it does work. I appreciate your help.

 

[PHV]

Why not simply this ?
CombinedFieldsList: Field1 & ";" + Field2 & ";" + Field3 & ";" + Field4 & ";" + Field5

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lss]

If you show:

CombinedFieldsList: Field1 & ";" + Field2 & ";" + Field3 & ";" + Field4 & ";" + Field5

and any fields are blank you would see:

Field1; Field2; ; ; ;

Then you have to chop off however many semicolons and spaces MAY appear...

 

[PHV]

Sorry, missread your post.
I thought the field(s) was null, not blank.
CombinedFieldsList: Field1 & IIf(Trim(Field2 & "")="", "", ";" & Field2 & IIf(Trim(Field3 & "")="", "", ";" & Field3 & IIf(Trim(Field4 & "")="", "", ";" & Field4 & IIf(Trim(Field5 & "")="", "", ";" & Field5))))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886