asked on

Split string when pattern an change

Hi!
i'm using Access 2003 and have a field in a table called NUM that can be look like any of the examples below:
1108
1010A
1010B
1020/1030/1040
1120/30/40/50/60

I am building a report and there are couple of results I want to display NUM in two columns, one for numbers and the other for suffixes. So, with the above examples, my report would look like:

1108 | |
1010 |A/B |
1020/1030/1040 | |

I was going to use regexp, but I am not sure how to set the pattern, because of the "/" that can appear unlimited times.

Thanks!

Jeffrey Coachman

In a query, do this something like this:

SELECT YourTable.YourField, IIf((Right([YourField],1)="A") Or (Right([YourField],1)="B"),"A/B","") AS Suffix
FROM YourTable;

...or
...create a public function like this:

Public Function AB(YourString As String) As String
If Right(YourString, 1) = "A" Or Right(YourString, 1) = "B" Then
AB = "A/B"
Else
AB = ""
End If
End Function

And call it in a query like this:
SELECT YourTable.YourField, AB([YourField]) AS Suffix
FROM YourTable;

JeffCoachman

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

...Then just use the report wizard to create the Report from this query...

ASKER

Thanks!