VB function to convert query text from Unicode

[Triacona]

Dear All,
Thanks for a great forum!
I have a function ( below ) which I plan to use to weed out Unicode character from a string.
The query feeds the array a string and then I work with it as below.

[COLOR=#204A87]Function[/color] ReplaceUniCode(strText [COLOR=#204A87]As String[/color])[COLOR=#204A87] As Variant[/color]
[COLOR=#4E9A06]' Replace % with the character you want to substitute.[/color]


[indent]
[COLOR=#204A87]Dim[/color] letters() [COLOR=#204A87]As String[/color]
[COLOR=#204A87]ReDim[/color] letters(Len(strText) - 1)
[COLOR=#204A87]Dim[/color] i [COLOR=#204A87]As Integer[/color]
[COLOR=#204A87]Dim[/color] ch [COLOR=#204A87]As Variant[/color]

[COLOR=#204A87]For[/color] i = 1 [COLOR=#204A87]To[/color] Len(strText)
letters(i - 1) = Mid$(strText, i, 1)
[COLOR=#204A87]Next[/color] i
[/indent]



[COLOR=#4E9A06][indent]
' For Each ch In letters
' If ch = vbUnicode Then
' ch = StrConv(ch, vbFromUnicode, 1)
' Else
' ch = ch
' End If
' Next ch
[/indent][/color]

ReplaceUniCode = letters




[COLOR=#204A87]End Function[/color]

I have commented out the For Each as I am still working with the first part of the function, in particular writing the strText to an array called letters
I am now getting an error though.

Subscript out of Range on:
letter(i - 1) = Subscript out of Range

I have put a break in and go through the loop, but it seems to give the above error.
I do not know why it goes out of range as I have specified the length of the array to be the string length...
Any help with this would be greatly appreciated.
Thanks.

Thank you,

Kind regards

Triacona

 

[Andrzejek]

I did not get any errors with your code:

Option Explicit

Sub test()
Dim strText  As String

strText = "This is my test string"

Dim letters() As String
ReDim letters(Len(strText) - 1)
Dim i As Integer
Dim ch As Variant

For i = 1 To Len(strText)
    letters(i - 1) = Mid$(strText, i, 1)
Next i

Debug.Print Join(letters(), "")

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[Triacona]

Hi All,

Thanks Andrzejek for your help, much appreciated

I think you are correct.

Please see below my delema.

I am trying to remove this ⊥ symbol ( Unicode )

[COLOR=#204A87]For Each[/color] ch [COLOR=#204A87]In[/color] letters
        [COLOR=#204A87]If[/color] ch = vbUnicode [COLOR=#204A87]Then[/color]
            ch = StrConv(ch, vbFromUnicode, 1)
        [COLOR=#204A87]Else[/color]
            ch = ch
        [COLOR=#204A87]End If[/color]
    [COLOR=#204A87]Next[/color] ch

    ReplaceUniCode = letters

So I have put a break in and the loop loops through and inserts into the letters array.
It does not seem to error there ( you are correct ), but for some reason if you hover over letters ( once the loop is completed ) the error appears...

But it moves to the next loop ( above ), so I am guessing it worked?

I am trying to check if ch contains any Unicode and if so convert it from Unicode to a space, or comma.

Is the If statement correct in doing this?

Thanks for all your help

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,
I have solved the problem with the following:

[indent][COLOR=#204A87]Dim[/color] i [COLOR=#204A87]As Integer[/color][/indent]
    [COLOR=#204A87]Dim[/color] output [COLOR=#204A87]As String[/color]
    [COLOR=#204A87]Dim[/color] character [COLOR=#204A87]As String[/color]

    
   [COLOR=#204A87] For[/color] i = 1 [COLOR=#204A87]To[/color] [COLOR=#729FCF]Len[/color](strText)
        character = [COLOR=#204A87]Mid[/color](strText, i, 1)
        [COLOR=#204A87]If[/color] (character >= "a" [COLOR=#204A87]And[/color] character <= "z") [COLOR=#204A87]Or[/color] (character >= "0" [COLOR=#204A87]And[/color] character <= "9") [COLOR=#204A87]Or[/color] (character >= "A" [COLOR=#204A87]And[/color] character <= "Z") [COLOR=#204A87]Then[/color]
            output = output & character
        [COLOR=#204A87]Else[/color]
            output = output & ", "

        [COLOR=#204A87]End If[/color]
    [COLOR=#204A87]Next[/color]
    
    ReplaceUniCode = output

Now all I want to do is split the string by comma and add each split to new columns in my query, do I use queryDef as DAO, I really do not know where to start?
I can use the Replace for the split or strSplit?
Thanks for all your help

Thank you,

Kind regards

Triacona

 

[Andrzejek]

I want to [...] split the string by comma" - you are very close

...
Dim aryS() As String
...
aryS = [blue]Split[/blue](output, ", ")

and you get an array of your portions of the [tt]output[/tt] split by comma.


---- Andy

There is a great need for a sarcasm font.

 

[lameid]

To get it in a query though...

One way would be to create a table with all the necessary fields (perhaps you determine number of fields and create them if needed as part of first procedure?
Then you could write the data into that table based on split the values... just loop over the elements of the array and assign them to fields, I am assuming numbered somehow. Then you can select from this. Although looking at this methodology, it may be less overhead to just put the values in directly by expanding your existing code.

Anther way would be to use a function to return each column..

Function fnReturnCol(strIn as string, colNum as long) as string
     fnReturnCol = Split(strIN, ", ")(colNum - 1) 'I used comma and space to be like your code concatenating it in.  
                                                  'Arrays are 0 based unless you specify them not to be so subtract 1 from desired column number
End function

Then your query looks something like the below. If you intend to run your query more than once, the first way is probably the way to go. The advantage here would be if the data is temporary and you don't want to keep it around.

SELECT fnReturnCol(SomeTable.FieldNeedingParsed,1), fnReturnCol(SomeTable.FieldNeedingParsed,2) 
FROM SomeTable