All geek questions in one place

Awkward DAO Manipulation Problem

I am working with an obsolete vb6 product, and I ran into a problem when I need to get part of the file name from the full path from the database table via DAO. I do not have access to VBA functions, so I am looking specifically for MS Access SQL. I have no way to reset the extra code after the request. I CANNOT change / reorganize the solution without changing SQL.

Now the DAO has no functions instrrevor replace, therefore, I'm pretty limited.

Any guesses? Thanks in advance.

+3
source share
3 answers

Assuming you cannot modify the actual database.,.

, ( , ) - instr, iif-, . inStrRev

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


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

, 10 . , 10 , vba, .

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

,

+2

vba, instr, replace, mid ..

"", - . , http://support.microsoft.com/kb/294698

+2

DAO, 10-15 ...

DAO Access QueryDef, ?

0
source

Source: https://habr.com/ru/post/1709305/

More articles:


All Articles