Suppressing Characters/Serialized arrays?

[map816]

I have data written to a table that creates a lot of extra characters called serialized arrays. A few examples are below:

a:2:{i:0;s:15:"Individual Work";i:1;s:16:"Small group work";}

a:2:{i:0;s:13:"Front of room";i:1;s:31:"Circulating throughout the room";}

a:1:{i:0;s:6:"Posted";}

Is it possible to create a query that will suppress/drop the extra characters and just give me the data in quotation marks? The data can be in separate fields or concantenated it doesn't matter. Looking for results like below:

Individual Work, Small group work

Front of room, Circulating throughout the room

Posted

Thanks.

 

[Galaxiom]

You will need a custom function.
In that function use the Split function with quote character as the delimiter and then concatenate the resulting array members up to 1 and 3 with a comma and space between.

http://office.microsoft.com/en-us/access-help/split-function-HA001228911.aspx

 

[map816]

I saw that split function link before with microsoft and thought that might be what I needed but struggled trying to figure out how to get it to work properly for me.

 

[Galaxiom]

Here is some code to get you started.
Put it in a standard module.

It assumes the first quoted section is not right at the start.

You will need to add error handling and deal with values that have no quotes in them.

Call it in your query with:

SomeName: ExtractSerialized([fieldname])

Function ExtractSerialized(InputString As String) As String
 
Dim SplitArray As Variant
Dim SplitArrayUB As Long
Dim n As Integer
 
SplitArray = Split(InputString, """")
SplitArrayUB = UBound(SplitArray)
 
n = 1
Do
   ExtractSerialized = ExtractSerialized & SplitArray(n) & ", "
   n = n + 2
Loop While n <= SplitArrayUB
 
ExtractSerialized = Left(ExtractSerialized, Len(ExtractSerialized) - 2)
 
End Function

 

[map816]

Thanks a bunch. I appreciate it. The sample code worked well for me. I didn't have to do any error checking with the data set I had input so far.

Mark

 

[map816]

Any help with handling fields with null values? Thanks a bunch.

 

[map816]

Anyone have any ideas how to modify this module to account for a field that is null? When the field is null the output from the split function is #error. Thanks.

 

[JANR]

Use Nz wrapper function to replace Nulls with a value which your function can handle.

SomeField: FunctionName(Nz([Field],"SomeValue"))

Put a value to replace the Null value, marked red.

JR

 

[map816]

How would I add this coding to the field if I am already running the ExtractSerialized function from above. Here is a call I am using in a query I have:

Activities_Use: ExtractSerialized(wp_frm_item_metas_Crosstab![109])

The field is Activities Use and the above referenced ExtractSerialized function is splitting field number 109 in my wp_frm_item_metas_Crosstab query? Thanks.

 

[JANR]

Well send the function an empty sting perhaps.

Activities_Use: ExtractSerialized(Nz(wp_frm_item_metas_Crosstab![109]),"""")


JR

 

[Galaxiom]

Another way is to change the function argument to a Variant datatype which will accept a Null. Then deal with the Null inside the function using the Nz.

Either way you are going to have to handle the problem that the NullString will split to an empty array so you will get a subscript out of range error from SplitArray.

 

[map816]

JANR,

I get a "wrong number of arguments used with function in query expression" error.

 

[Galaxiom]

Activities_Use: ExtractSerialized(Nz(wp_frm_item_metas_Crosstab![109]),"""")
Too many double quote marks in JANR's post.

Should be

Activities_Use: ExtractSerialized(Nz(wp_frm_item_metas_Crosstab![109]),"")

 

[map816]

Galaxiom,

Would I add something like this to the function? Thanks.

Dim varVariant As Variant
varVariant = Null

Debug.Print Nz(varVariant) 'returns a Zero length String
Debug.Print Nz(varVariant, "Null") 'returns the String Null

 

[Galaxiom]

Function ExtractSerialized([COLOR=blue]InputValue As Variant[/COLOR]) As String
 
Dim SplitArray As Variant
Dim SplitArrayUB As Long
Dim n As Integer

 
   [COLOR=blue]InputValue = Nz(InputValue, "")[/COLOR]
 
 [COLOR=blue]  If InputValue = "" Then[/COLOR]
[COLOR=blue]      ExtractSerialized = ""[/COLOR]
[COLOR=blue]      Exit Function[/COLOR]
[COLOR=blue]   End If[/COLOR]
 
   SplitArray = Split([COLOR=blue]InputValue[/COLOR], """")
 
etc

 

[map816]

Thanks a bunch. This seems to work for the function:

Option Compare Database

Function ExtractSerialized(InputValue As Variant) As String

Dim SplitArray As Variant
Dim SplitArrayUB As Long
Dim n As Integer


InputValue = Nz(InputValue, "")

If InputValue = "" Then
ExtractSerialized = ""
Exit Function
End If

SplitArray = Split(InputValue, """")
SplitArrayUB = UBound(SplitArray)

n = 1
Do
ExtractSerialized = ExtractSerialized & SplitArray & ", "
n = n + 2
Loop While n <= SplitArrayUB

ExtractSerialized = Left(ExtractSerialized, Len(ExtractSerialized) - 2)

End Function

 

[JANR]

Activities_Use: ExtractSerialized(Nz(wp_frm_item_metas_Crosstab![109]),"""")
Too many double quote marks in JANR's post.

Well actually no, but the syntax for Nz was wrong, should be:

Activities_Use: ExtractSerialized(Nz(([wp_frm_item_metas_Crosstab![109]),""""))

What I was sending to the original function was a literal doublequotes to stop it from bombing on the splitArray.

You could still be using the original function if you wanted to keep Inputstring typecast as String rather than a Variant, by adding a test for vbNullString

Function ExtractSerialized(InputString As String) As String
 
Dim SplitArray As Variant
Dim SplitArrayUB As Long
Dim n As Integer
 
If InputString = vbNullString Then Exit Function
.... Rest of code
End Function

Then from the query:

Activities_Use: ExtractSerialized(Nz(([wp_frm_item_metas_Crosstab![109]),""))

JR

 

[Galaxiom]

What I was sending to the original function was a literal doublequotes to stop it from bombing on the splitArray.

Ah, of course. Been too long since the original discussion to recall the context of the problem.