Reversing words in string, last word to 1st ... (1 Viewer)

[Timrock]

I have a AD data in a table. The data looks like this:

Users.Fleet Accounts Div.Financial Accounts Group.HeadOffice

the period (.) separates the Organsation units

It needs to be changed to look like this:

Fleet Accounts Div.Financial Accounts Group.HeadOffice.Users.

Is there way any to to do this in an expression?

The StrReverse is great for each character. How can I make this work on aword?

 

[Timrock]

oops.

The data changed data should look like this:

HeadOffice.Financial Accounts Group.Fleet Accounts Div.Users

 

[gemma-the-husky]

here is a neat little recursive function.

basically it finds the term after the last separator, and then repeats the process for the string up to the last separator - so it works on a string of any length - although it is sometimes difficult to see exactly what is going on with recursion

Function revstrg(s As String, sep As String) As String
    If InStr(s, sep) = 0 Then
        revstrg = s
    Else
        revstrg = Mid(s, InStrRev(s, sep) + 1) & sep & revstrg(Left(s, InStrRev(s, sep) - 1), sep)
    End If
End Function

use it by calling the function with the string, and the separator within the string

eg

Sub reverseterms()
Dim s As String
 
s = "Users.Fleet Accounts Div.Financial Accounts Group.HeadOffice"
 
 
[B]MsgBox (revstrg(s, "."))[/B]
 
End Sub

 

[Timrock]

Wow, that was quick. It gives me hope, this can be done!

I am a bit slow digesting your post. Can this be done in an Access 2007 expression?

 

[gemma-the-husky]

you would need to put the revstrg bit in a module.

then in a query just say in the column header, something like

Reversed: revstrg([fieldname],".")

 

[Timrock]

Thanks am getting stuck into brushing up on the module bit now and will give you feedback.

I appreciate your help

 

[Timrock]

Thanks for your advice / suggestion. The code worked 100%. Fantastic