One Column Value needs multiple select values

[mail2poov]

Hello,

expecting your expertise in query to solve this

Table = TblFolders contains field1 (Only one field)
Values - Field1 = "E:\2TB\iCloud\Utilities\Bas\abcdef\"

Requirement:
Select a,b,c,d,e from TblFolders - expected Result is
consider "/" as delimiter and the select result should be
a= E:
b=2TB
c=iCloud
d=Utilities
e=Bas\abcdef (ie last column contains rest of the value)

Please let me know if you need more info.

Thanks
Poov

 

[JHB]

Paste the following code into a module.

Public Function FindABCDE(FieldText, FieldNo As Long)
  Dim spltStr() As String, RestText As String, x As Integer
  
  If FieldText <> "" Then
    spltStr = Split(FieldText, "\")
    If FieldNo < 4 And UBound(spltStr) - 1 >= FieldNo Then
      FindABCDE = spltStr(FieldNo)
    ElseIf FieldNo = 4 And UBound(spltStr) - 1 >= FieldNo Then
      For x = 4 To UBound(spltStr)
        If spltStr(x) <> "" Then
          RestText = RestText & spltStr(x) & "\"
        End If
      Next x
      FindABCDE = left(RestText, Len(RestText) - 1)
    Else
      If spltStr(UBound(spltStr)) <> "" And UBound(spltStr) = FieldNo Then
        FindABCDE = spltStr(UBound(spltStr))
      End If
    End If
  End If
End Function

And this into a query

SELECT FindABCDE([field1],0) AS A, FindABCDE([field1],1) AS B, FindABCDE([field1],2) AS C, FindABCDE([field1],3) AS D, FindABCDE([field1],4) AS E
FROM TblFolders;

 

[mail2poov]

Thanks JHB. It is working fine in Access.

But my requirement is, I am executing from excel. The real scenario is:

1) Table tbl2TB_Files contains FName, Fpath fields. The value of the FName is file name and FPath is the full Path of the file.

2) From excel i pass the query to fetch value from access and populate in excel.

3) I copied FindABCDE public function in MS access and run the excel it says Run time error - Undefined function 'FindABCDE' in expression.

4) Again i copied the FindABCDE function in excel vba module - (I know it it will not work) - it is also not working.

The sql what i pass from excel is:
SELECT FindABCDE([Fpath],0) AS A, FindABCDE([Fpath],1) AS B, FindABCDE([Fpath],2) AS C, FindABCDE([Fpath],3) AS D, FindABCDE([Fpath],4) AS E FROM [;DATABASE=C:\APTP\2TB_Sync\iCloud\Utilities\FileManager_Access\V_0.1.1\FileManager.accdb].tbl2TB_Files


ALTERNATE METHOD 2
- i Created the FindABCDE function and query in access itself - from excel i call the query like

ssql = "SELECT top 10 * FROM " & scn & ".TP_Files_Query1"


this also gives the same error

 

[mail2poov]

As per msdn - MS access query having User defined functions will not be calling from other applications:
social.msdn.microsoft.com/Forums/en-US/accessdev/thread/9d6af5be-be49-4584-b2ff-130b1d4bb5b2