Remove Characters from Access Field

[kparadise]

I have a field on a form which is a drop down field linked to a reference table. Only one item can be selected, and the field is a CONCATE of four different columns:

RootCause: [tblRefRootCause]![Ref No] & " | " & [tblRefRootCause]![Root Cause Level 1] & " | " & [tblRefRootCause]![Root Cause Level 2] & " | " & [tblRefRootCause]![Root Cause Level 3]

When I pull this field into a query and reports, it works great. However, what I am trying to do is remove the first 5 characters of this CONCATE field. I am trying to use MID, and LEN formulas I found on the internet, but I am getting FUNC! errors..

 

[Norie]

To remove the first 5 characters all you should need is Mid.

RootCause: Mid([tblRefRootCause]![Ref No] & " | " & [tblRefRootCause]![Root Cause Level 1] & " | " & [tblRefRootCause]![Root Cause Level 2] & " | " & [tblRefRootCause]![Root Cause Level 3],6)

or

Mid(RootCause,6)

 

[kparadise]

Ok, using the Mid(RootCause, 6) leaves my with blanks (no results).

I want the first 5 characters to be utilized in the drop down menu (used for referencing), but when I put that field on the report, I do not need those first 5. So, using your first option will not work.

 

[Norie]

Sorry I don't follow, what's the connection between the dropdown, the report and the field?

 

[kparadise]

So my reference table is tblRefRootCause:

Ref No	Root Cause Level 1	Root Cause Level 2	Root Cause Level 3
1a	People	Staffing	Training
1b	People	Staffing	Resources
2a	System	Technology	Not Updated

<tbody>
</tbody>

My Form has a drop down list connected to this table. SELECT tblRefRootCause.[Ref No], [tblRefRootCause]![Ref No] & " | " & [tblRefRootCause]![Root Cause Level 1] & " | " & [tblRefRootCause]![Root Cause Level 2] & " | " & [tblRefRootCause]![Root Cause Level 3] AS RootCause FROM tblRefRootCause ORDER BY tblRefRootCause.[Ref No];

This form Saves to my tbl_ISSUES. [tbl_ISSUES].[Root Cause]. This is the field I pull into my query and report.