Replacing characters in strings

J

Judy Ward

I have an array that I use as the criteria for a query (the real array is
much longer):
IR_List(1) = "IR-7"
IR_List(2) = "IR-8"
IR_List(3) = "IR-9"
IR_List(4) = "IR-9.1"
But then I want to use these same designations as individual sheet names,
and Excel does not accept periods, spaces or dashes in a sheet name. So I
have been using a second array:
Sheet_List(1) = "IR_7"
Sheet_List(2) = "IR_8"
Sheet_List(3) = "IR_9"
Sheet_List(4) = "IR_9_1"

I thought it would be more efficient to use one array and convert the
characters so I wrote a function to do so:
Function ReplaceChars(ByRef strText As String)
Dim currLoc As Integer
Dim tmpChar As String
For currLoc = 1 To Len(strText)
tmpChar = Mid(strText, currLoc, 1)
If InStr(".", tmpChar) Or InStr("-", tmpChar) Or InStr(" ", tmpChar)
Then
' Replace with a "_"
Mid(strText, currLoc, 1) = "_"
End If
Next
ReplaceChars = strText
End Function

This function does replace the characters. But when I try to use it as part
of a SQL query replacing:
& "INTO " & sCnxn & "]." & Sheet_List(idx) _
with
& "INTO " & sCnxn & "]." & ReplaceChars(IR_List(idx)) _
does not work.

For some reason Sheet_List(idx) translates into IR-7
but ReplaceChars(IR_List(idx)) translates into "IR-7"
(quotes arount the string) and the query doesn't return any data.

If anyone can follow what I am trying to ask, I would really appreciate
knowing if there is something simple I am overlooking to get this to work
(not have quotes around ReplaceChars(IR_List(idx))).

Thank you,
Judy
 
W

Wayne Morgan

What happens if you define the function as a string?

Function ReplaceChars(ByRef strText As String) As String

Have you tried the built-in Replace() function (Access 2000 and newer)? Have
you done a Debug.Print of the final SQL result to verify what is actually in
the SQL string?
 
J

Judy Ward

Thank you for your reply. Yes, I am using Debug.Print strSQL--that is where
I saw the quote marks show up.

I just tried changing the function to return a string. Doing that did get
rid of the quote marks. I have tried running the query both ways now--the
only difference in the code is the line I quoted below. Debug.Print returns
exactly the same results. But for some reason which escapes me, using
ReplaceChars(IR_List(idx)) gives me empty spreadsheets while Sheet_List(idx)
returns the expected results.

I have not tried the built-in Replace function even though I am using Access
2003. If you can point me towards an example that does something similar to
what I am trying to do or if you can give me an example of how I would use
this function to replace all instances of a space, period or dash in a
string--that would be wonderful.

Thank you very much for your help,
Judy

Wayne Morgan said:
What happens if you define the function as a string?

Function ReplaceChars(ByRef strText As String) As String

Have you tried the built-in Replace() function (Access 2000 and newer)? Have
you done a Debug.Print of the final SQL result to verify what is actually in
the SQL string?

--
Wayne Morgan
MS Access MVP


Judy Ward said:
I have an array that I use as the criteria for a query (the real array is
much longer):
IR_List(1) = "IR-7"
IR_List(2) = "IR-8"
IR_List(3) = "IR-9"
IR_List(4) = "IR-9.1"
But then I want to use these same designations as individual sheet names,
and Excel does not accept periods, spaces or dashes in a sheet name. So I
have been using a second array:
Sheet_List(1) = "IR_7"
Sheet_List(2) = "IR_8"
Sheet_List(3) = "IR_9"
Sheet_List(4) = "IR_9_1"

I thought it would be more efficient to use one array and convert the
characters so I wrote a function to do so:
Function ReplaceChars(ByRef strText As String)
Dim currLoc As Integer
Dim tmpChar As String
For currLoc = 1 To Len(strText)
tmpChar = Mid(strText, currLoc, 1)
If InStr(".", tmpChar) Or InStr("-", tmpChar) Or InStr(" ",
tmpChar)
Then
' Replace with a "_"
Mid(strText, currLoc, 1) = "_"
End If
Next
ReplaceChars = strText
End Function

This function does replace the characters. But when I try to use it as
part
of a SQL query replacing:
& "INTO " & sCnxn & "]." & Sheet_List(idx) _
with
& "INTO " & sCnxn & "]." & ReplaceChars(IR_List(idx)) _
does not work.

For some reason Sheet_List(idx) translates into IR-7
but ReplaceChars(IR_List(idx)) translates into "IR-7"
(quotes arount the string) and the query doesn't return any data.

If anyone can follow what I am trying to ask, I would really appreciate
knowing if there is something simple I am overlooking to get this to work
(not have quotes around ReplaceChars(IR_List(idx))).

Thank you,
Judy
Click to expand...
Click to expand...
 
W

Wayne Morgan

To rewrite your function using the built-in Replace() function.

Function ReplaceChars(ByVal strText As String) As String
strText = Replace(strText, ".", "_")
strText = Replace(strText, "-", "_")
ReplaceChars = Replace(strText, " ", "_")
End Function

Or, you could do this right in the SQL concatenation:

& "INTO " & sCnxn & "]." & Replace(Replace(Replace(IR_List(idx), ".", "_"),
"-", "_"), " ", "_") _

Example from the Immediate window:
?Replace(Replace(Replace("a.b c-d", ".", "_"), "-", "_"), " ", "_")
a_b_c_d

--
Wayne Morgan
MS Access MVP


Judy Ward said:
Thank you for your reply. Yes, I am using Debug.Print strSQL--that is
where
I saw the quote marks show up.

I just tried changing the function to return a string. Doing that did get
rid of the quote marks. I have tried running the query both ways now--the
only difference in the code is the line I quoted below. Debug.Print
returns
exactly the same results. But for some reason which escapes me, using
ReplaceChars(IR_List(idx)) gives me empty spreadsheets while
Sheet_List(idx)
returns the expected results.

I have not tried the built-in Replace function even though I am using
Access
2003. If you can point me towards an example that does something similar
to
what I am trying to do or if you can give me an example of how I would use
this function to replace all instances of a space, period or dash in a
string--that would be wonderful.

Thank you very much for your help,
Judy

Wayne Morgan said:
What happens if you define the function as a string?

Function ReplaceChars(ByRef strText As String) As String

Have you tried the built-in Replace() function (Access 2000 and newer)?
Have
you done a Debug.Print of the final SQL result to verify what is actually
in
the SQL string?

--
Wayne Morgan
MS Access MVP


Judy Ward said:
I have an array that I use as the criteria for a query (the real array
is
much longer):
IR_List(1) = "IR-7"
IR_List(2) = "IR-8"
IR_List(3) = "IR-9"
IR_List(4) = "IR-9.1"
But then I want to use these same designations as individual sheet
names,
and Excel does not accept periods, spaces or dashes in a sheet name.
So I
have been using a second array:
Sheet_List(1) = "IR_7"
Sheet_List(2) = "IR_8"
Sheet_List(3) = "IR_9"
Sheet_List(4) = "IR_9_1"

I thought it would be more efficient to use one array and convert the
characters so I wrote a function to do so:
Function ReplaceChars(ByRef strText As String)
Dim currLoc As Integer
Dim tmpChar As String
For currLoc = 1 To Len(strText)
tmpChar = Mid(strText, currLoc, 1)
If InStr(".", tmpChar) Or InStr("-", tmpChar) Or InStr(" ",
tmpChar)
Then
' Replace with a "_"
Mid(strText, currLoc, 1) = "_"
End If
Next
ReplaceChars = strText
End Function

This function does replace the characters. But when I try to use it as
part
of a SQL query replacing:
& "INTO " & sCnxn & "]." & Sheet_List(idx) _
with
& "INTO " & sCnxn & "]." & ReplaceChars(IR_List(idx)) _
does not work.

For some reason Sheet_List(idx) translates into IR-7
but ReplaceChars(IR_List(idx)) translates into "IR-7"
(quotes arount the string) and the query doesn't return any data.

If anyone can follow what I am trying to ask, I would really appreciate
knowing if there is something simple I am overlooking to get this to
work
(not have quotes around ReplaceChars(IR_List(idx))).

Thank you,
Judy
Click to expand...
Click to expand...
Click to expand...
 
J

Judy Ward

Thank you very much for going the extra mile and providing these examples for
me. It really helps and I look forward to trying them out tomorrow.

Thank you,
Judy

Wayne Morgan said:
To rewrite your function using the built-in Replace() function.

Function ReplaceChars(ByVal strText As String) As String
strText = Replace(strText, ".", "_")
strText = Replace(strText, "-", "_")
ReplaceChars = Replace(strText, " ", "_")
End Function

Or, you could do this right in the SQL concatenation:

& "INTO " & sCnxn & "]." & Replace(Replace(Replace(IR_List(idx), ".", "_"),
"-", "_"), " ", "_") _

Example from the Immediate window:
?Replace(Replace(Replace("a.b c-d", ".", "_"), "-", "_"), " ", "_")
a_b_c_d

--
Wayne Morgan
MS Access MVP


Judy Ward said:
Thank you for your reply. Yes, I am using Debug.Print strSQL--that is
where
I saw the quote marks show up.

I just tried changing the function to return a string. Doing that did get
rid of the quote marks. I have tried running the query both ways now--the
only difference in the code is the line I quoted below. Debug.Print
returns
exactly the same results. But for some reason which escapes me, using
ReplaceChars(IR_List(idx)) gives me empty spreadsheets while
Sheet_List(idx)
returns the expected results.

I have not tried the built-in Replace function even though I am using
Access
2003. If you can point me towards an example that does something similar
to
what I am trying to do or if you can give me an example of how I would use
this function to replace all instances of a space, period or dash in a
string--that would be wonderful.

Thank you very much for your help,
Judy

Wayne Morgan said:
What happens if you define the function as a string?

Function ReplaceChars(ByRef strText As String) As String

Have you tried the built-in Replace() function (Access 2000 and newer)?
Have
you done a Debug.Print of the final SQL result to verify what is actually
in
the SQL string?

--
Wayne Morgan
MS Access MVP


I have an array that I use as the criteria for a query (the real array
is
much longer):
IR_List(1) = "IR-7"
IR_List(2) = "IR-8"
IR_List(3) = "IR-9"
IR_List(4) = "IR-9.1"
But then I want to use these same designations as individual sheet
names,
and Excel does not accept periods, spaces or dashes in a sheet name.
So I
have been using a second array:
Sheet_List(1) = "IR_7"
Sheet_List(2) = "IR_8"
Sheet_List(3) = "IR_9"
Sheet_List(4) = "IR_9_1"

I thought it would be more efficient to use one array and convert the
characters so I wrote a function to do so:
Function ReplaceChars(ByRef strText As String)
Dim currLoc As Integer
Dim tmpChar As String
For currLoc = 1 To Len(strText)
tmpChar = Mid(strText, currLoc, 1)
If InStr(".", tmpChar) Or InStr("-", tmpChar) Or InStr(" ",
tmpChar)
Then
' Replace with a "_"
Mid(strText, currLoc, 1) = "_"
End If
Next
ReplaceChars = strText
End Function

This function does replace the characters. But when I try to use it as
part
of a SQL query replacing:
& "INTO " & sCnxn & "]." & Sheet_List(idx) _
with
& "INTO " & sCnxn & "]." & ReplaceChars(IR_List(idx)) _
does not work.

For some reason Sheet_List(idx) translates into IR-7
but ReplaceChars(IR_List(idx)) translates into "IR-7"
(quotes arount the string) and the query doesn't return any data.

If anyone can follow what I am trying to ask, I would really appreciate
knowing if there is something simple I am overlooking to get this to
work
(not have quotes around ReplaceChars(IR_List(idx))).

Thank you,
Judy
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Export text to TextBox into Excel 0
Split Characters in Textbox 9
Windows Clipboard Read - Access application hanging 0
Code OK in XL2003 but Run-time error '1004' in xl2007 0
type mismatch error in macro in excel 0
Extracting keywords from a string 8
listbox align right 1
Substitute Respective Entries In Arrays 7

Top