Query Expression Trim

[adam.greer]

Hi Guys

Another expression that's troubling me. I need an expression that can take this field.

Z:\Music\Download\D\Dale Davies\Untitled EP\Dale Davies - Untitled EP - 05 - No Perfect Child.mp3

Trim the front to the 4th \ ie. Dale Davies\Untitled EP\Dale Davies - Untitled EP - 05 - No Perfect Child.mp3

Then the end of it ie Dale Davies

So all I have left in a seperate field is the Name. Also I'd like to know what parameters to change to trim to the 2nd or 3rd \.

Thanks alot guys

Adam Greer

 

[allan57]

Hi Adam, try the following

Option Compare Database
Option Explicit

Private Sub Form_Load()
Dim strGetStringToWorkWith As String

strGetStringToWorkWith = "Z:\Music\Download\D\Dale Davies\Untitled EP\Dale Davies - Untitled EP - 05 - No Perfect Child.mp3"

MsgBox Mid(strGetStringToWorkWith, LocateCharOccurancePositionInString("\", 4, strGetStringToWorkWith))

End Sub

Public Function LocateCharOccurancePositionInString(FindChar As String, OccurancePosition As Integer, StringToWorkWith As String) As Integer
Dim intOccuranceCounter As Integer
Dim intOccurancePositionCounter As Integer

On Error Resume Next

intOccurancePositionCounter = 0

For intOccuranceCounter = 1 To OccurancePosition

intOccurancePositionCounter = InStr(intOccurancePositionCounter + 1, StringToWorkWith, FindChar)

Next intOccuranceCounter

LocateCharOccurancePositionInString = intOccurancePositionCounter + 1

End Function

 

[adam.greer]

I was hoping for a more simple expression rather then a piece of code. I'm not very familiar with using code to manipulate text. I just wanted to put a line in a query.

Thanks for your help though

 

[neileg]

Once you have saved the function (in a module) it does become a line in a query!

 

[Bat17]

try this Function.

Function GetPathSection(StrPath, intSection) As String
Dim aPath() As String
On Error Resume Next
If Nz(StrPath, "") = "" Then Exit Function 'no data!
aPath = Split(StrPath, "\")
GetPathSection = aPath(intSection - 1)
End Function

save it in a module and make sure you don't save the module with the same name as the Function or Access will spit its dummy out!

to use it from a query call it like a built in function

MyBit:GetPathSection([MyField],5)

HTH

Peter

 

[adam.greer]

Works perfectly thanks Peter