Awkward DAO string manipulation issue.

Question

I'm working with a legacy vb6 product and I've come across a problem whereby I need to get the filename part of a full path from a database table through DAO. I've got no access to VBA functions here so I'm looking specifically for MS Access SQL. I have no way of dropping some extra code after the query. I CAN'T change/refactor the solution short of modifying the SQL.

Now, DAO doesn't have any instrrev or replace functionality so I'm pretty limited.

Any guesses out there? Thanks in advance.

Answer 1

I used to do a lot of DAO programming, but that was 10-15 years ago...

Can you change the DAO to call an Access QueryDef and then fix it up there?

Answer 2

You should be able to use the built-in vba functions like instr, replace, mid, etc.

There is a "sandbox" mode that may block them - see this on how to unblock them http://support.microsoft.com/kb/294698

Answer 3

Assuming you can't change the actual database . . .

The only thing I can think of (and I wracked by brains on this one, sorry mate) is to use repeated calls to instr, nested in iif statements e.g. to replace this call to inStrRev

SELECT IIf(InStr([FileName],""\"")>0,Mid$([Filename],InStrRev([Filename],""\"")+1),[Filename]) FROM Table1

You'd have a compeltely insane

SELECT IIf(InStr([FileName],""\"")>0,Mid$([Filename],iif(InStr(1, [FileName], ""\"") > 0, iif(InStr(2, [FileName], ""\"") > 0, iif(InStr(3, [FileName], ""\"") > 0, iif(InStr(4, [FileName], ""\"") > 0, iif(InStr(5, [FileName], ""\"") > 0, iif(InStr(6, [FileName], ""\"") > 0, iif(InStr(7, [FileName], ""\"") > 0, iif(InStr(8, [FileName], ""\"") > 0, iif(InStr(9, [FileName], ""\"") > 0, 1, InStr(9, [FileName], ""\"")), InStr(8, [FileName], ""\"")), InStr(7, [FileName], ""\"")), InStr(6, [FileName], ""\"")), InStr(5, [FileName], ""\"")), InStr(4, [FileName], ""\"")), InStr(3, [FileName], ""\"")), InStr(2, [FileName], ""\"")), InStr(1, [FileName], ""\""))),[Filename]) from table1

This will work for a path thats 10 or so sub folders deep. If you think 10 sub folders is too little, I've a bit of vba to generate the statement to what ever depth you require.

Function BuildNestedIIfs(ByVal depth As Integer, byval maxDepth as integer) As String
    Dim locator As String
    If depth < maxDepth Then
        locator = "InStr(" & depth & ", [FileName], """"\"""")"
        Build = "iif(" & locator & " > 0, " & Build(depth + 1, maxDepth) & ", " & locator & ")"
    Else
       Build = "0"
    End If
End Function

It is obscene, but should work