Can I split a string or use the split function in design view?

[Michelle]

I'm not familiar with SQL. I tried to figure out how to incorporate the code
for the split funtion in the SQL view, but I didn't have any luck. Is there
any way I can split up the info from one field in the design view of a query?

I've tried to simply type the function "Split([FieldName]," ") in the field
row, to split up the string at the spaces, but that didn't work. What am I
doing wrong?

Thanks,

Michelle

 

[KARL DEWEY]

You can use the Left, Right, and Mid functions to split it into varrious size
pieces,
Also if you include the InStr function you can control where to split the
string.

 

[John W. Vinson]

I'm not familiar with SQL. I tried to figure out how to incorporate the code
for the split funtion in the SQL view, but I didn't have any luck. Is there
any way I can split up the info from one field in the design view of a query?

I've tried to simply type the function "Split([FieldName]," ") in the field
row, to split up the string at the spaces, but that didn't work. What am I
doing wrong?

Thanks,

Michelle

It's not easy to use the Split() function directly in a Query, since it
returns an array - which SQL cannot recognize or handle.

You can use a wrapper function:

Public Function NthElement(strIn As String, strDelim As String, _
iPos As Integer) As Variant
NthElement = Split(strIn, iPos, strDelim)
End Function

E.g. you can use NthElement([CompositeField], " ", 3) to return the third
"word" in a blank-delimited string in CompositeField.

 

[Michelle]

Thanks so much, both of you! This will really help me out a lot.

You can use the Left, Right, and Mid functions to split it into varrious size
pieces,
Also if you include the InStr function you can control where to split the
string.

I'm not familiar with SQL. I tried to figure out how to incorporate the code
for the split funtion in the SQL view, but I didn't have any luck. Is there
any way I can split up the info from one field in the design view of a query?

I've tried to simply type the function "Split([FieldName]," ") in the field
row, to split up the string at the spaces, but that didn't work. What am I
doing wrong?

Thanks,

Michelle