splitting text in query with delimiter (1 Viewer)

[frederik]

Hi,

I have a field in a table from which I want to split the value in multiple strings.
Delimiter is ">".

Example input: "[string1] > [string2] > [string3] > [string4]"
wanted output: variable1 has value [string1], variable2 has value [string2], etc...

In this forum, I found this public function: (thanks to boblarson)

Option Compare Database

Public Function SplitFile(intField As Integer, strValue As String, strDelimiter As String) As String
Dim varSplit As Variant

varSplit = Split(strValue, strDelimiter, , vbTextCompare)

SplitFile = varSplit(intField)

End Function

Boblarson then elaborated you have to use this syntax in the query:

MyField1:SplitFile(1,[YourUnSplitFieldName],",")
MyField2:SplitFile(2,[YourUnSplitFieldName],",")
etc ...

I've copied this code to a module, named the module "mod1".
Then in a query, I create a new column with following fieldname:

test1: SplitFile(1, [fieldA], ">")

Everytime I want to validatie this line, Access says:
"The expression you entered contains invalid syntax.
You may have entered an operand without an operator."

I tried it with following line, just to test:

test1: SplitFile(1, "test1,test2", ",")

But Access keeps firing the same error message box.
Also using semicolon instead of a comma doesn't help.

Does anyone know what I'm doing wrong here?
(using MS Access 2016)

 

[arnelgp]

the function should be modified to:

Option Compare Database

Public Function SplitFile(intField As Integer, strValue As Variant, strDelimiter As String) As String
Static varSplit As Variant
Static thisValue As String

strValue = strValue & "" 
If strValue = vbNullString Then Exit Sub
If thisValue <> strValue

	varSplit = Split(strValue, strDelimiter, , vbTextCompare)
	thisValue = strValue

	if UBound(varSplit) >= intField - 1

		SplitFile = [COLOR="Blue"]varSplit(intField - 1)[/COLOR]
	end if

Else
	if UBound(varSplit) >= intField - 1

		SplitFile = [COLOR="Blue"]varSplit(intField - 1)[/COLOR]
	end if
End If	
End Function

so SplitFile(1,[field],","), means the first splitted string to return.

you are missing double quote for your test:

test1: SplitFile(1, "test1,test2", ",")


EDIT: made some modification to make it more faster.

 

[frederik]

Thank you arnelgp.

I keep having an error with the new function. (this time it's a compile error)
I've attached my test-database.

There is 1 query (Query1) where I want to add this line:
field1: SplitFile(1,[fld1],",")

But it fires an error.

I can't see what's the culprit, can you?

 

[arnelgp]

sorry, forget the "Then" on the If condition.

 

[frederik]

gracias arnelgp

 

[arnelgp]

No hay problema

 

[frederik]

sorry arnelgp, i have to "unsovle" my issue.


on your last attachment, I see you succesfully entered 3 columns in the query with the function, which works very fine.
(that's when I marked the issue as solved).


something very strange is happening now: when I delete one column, e.g. the last one you created (field3), from the query (I use cut) and I paste it again, the same error fires again (see first post) in my Access 2016.
So pasting the very same syntax you entered doesn't work anymore.
I don't understand.
Do you know in which direction I should search?

 

[frederik]

No hay roblema

I found out that Access 2016 is a little quirky.

When I input the line field3: SplitFile(3,[fld1],",")
it doesn't work in Access 2016, but when doing it in Access 2010, no problem at all !


So arnelgp, I suppose you do not run Access 2016?

 

[arnelgp]

i cant recreate the problem. i am also using a2016 x64.

 

[frederik]

i cant recreate the problem. i am also using a2016 x64.

really bizarre.
I tried it again this morning but still the same behaviour: after pressing enter, computer says no : "The expression you entered contains invalid syntax.
You may have entered an operand without an operator".
The focus of the cursor is then on [fld1] or on "test1,test2" - which means that the problem is somewhere there.


I would like to take the time to investigate this further - for now I'm doing the splitting directly in VBA via this sub (after creating 5 additional columns in the table):

Private Sub Command0_Click()

Dim db
Dim rs
Dim varsplit As Variant
 Dim i, j
Dim veld

 

Set db = CurrentDb()
Set rs = db.OpenRecordset("voorwerpen")

rs.MoveFirst

For i = 1 To rs.RecordCount

If Not IsNull(rs("functie")) Then
    
    rs.Edit

     varsplit = Split(rs("functie"), ">", , vbTextCompare)[INDENT]   For j = 0 To UBound(varsplit)
[/INDENT][INDENT][INDENT]     veld = "fld" & (j + 1)
[/INDENT][INDENT]     rs(veld) = varsplit(j)
[/INDENT]Next
[/INDENT][INDENT]rs.Update
[/INDENT]End If

rs.MoveNext
Next

End Sub

 

[arnelgp]

sometimes "Missing Reference" can cause errors.
on VBA->Tools->References, see if you have some and try to resolve or delete them.

 

[frederik]

sometimes "Missing Reference" can cause errors.
on VBA->Tools->References, see if you have some and try to resolve or delete them.

Thank you for your suggestion. It didn't help.
I found the issue: it has nothing to do with which MS Access version is installed or VBA, but with the Windows regional settings. Mine were set for English(Belgium), which caused the issue.
Changing the format to English(United States) solved the issue.