×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VB function to convert query text from Unicode

VB function to convert query text from Unicode

VB function to convert query text from Unicode

(OP)
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.

CODE

Function ReplaceUniCode(strText As String) As Variant
' Replace % with the character you want to substitute.


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
' For Each ch In letters ' If ch = vbUnicode Then ' ch = StrConv(ch, vbFromUnicode, 1) ' Else ' ch = ch ' End If ' Next ch
ReplaceUniCode = letters End Function

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.

Quote (error)


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. smile
Thanks.

Thank you,

Kind regards

Triacona

RE: VB function to convert query text from Unicode

I did not get any errors with your code:

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.

RE: VB function to convert query text from Unicode

(OP)
Hi All,

Thanks Andrzejek for your help, much appreciated bigsmile

I think you are correct.

Please see below my delema.

I am trying to remove this ⊥ symbol ( Unicode )

CODE --> VBA

For Each ch In letters
        If ch = vbUnicode Then
            ch = StrConv(ch, vbFromUnicode, 1)
        Else
            ch = ch
        End If
    Next 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 smile

Thank you,

Kind regards

Triacona

RE: VB function to convert query text from Unicode

(OP)
Dear All,
I have solved the problem with the following:

CODE --> vba

Dim i As Integer
Dim output As String Dim character As String For i = 1 To Len(strText) character = Mid(strText, i, 1) If (character >= "a" And character <= "z") Or (character >= "0" And character <= "9") Or (character >= "A" And character <= "Z") Then output = output & character Else output = output & ", " End If Next 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 smile

Thank you,

Kind regards

Triacona

RE: VB function to convert query text from Unicode

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

CODE

...
Dim aryS() As String
...
aryS = Split(output, ", ") 

and you get an array of your portions of the output split by comma.


---- Andy

There is a great need for a sarcasm font.

RE: VB function to convert query text from Unicode

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..

CODE

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.

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close