Sorry for re-posting this code...
Anyway try this:
1) Paste this code 2 function to a new module
==== code start ===
'This function replace the built-in Replace function:
Function FindAndReplace(ByVal strInString As String, _
strFindString As String, _
strReplaceString As String) As String
Dim intPtr As Integer
'***DEBUG.PRINT strInString
If Len(strFindString) > 0 Then 'catch if try to find empty string
Do
intPtr = InStr(strInString, strFindString)
If intPtr > 0 Then
FindAndReplace = FindAndReplace & Left(strInString, intPtr -
1) & _
strReplaceString
strInString = Mid(strInString, intPtr +
Len(strFindString))
End If
Loop While intPtr > 0
End If
FindAndReplace = FindAndReplace & strInString
End Function
Function ftnRunReplace(strMyTableName As String, strMyFieldName As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
strSQL = "SELECT * FROM " & strMyTableName
rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then
Else
While Not rs.EOF
rs(strMyFieldName) = FindAndReplace(rs(strMyFieldName), " ",
",")
rs.Update
rs.MoveNext
Wend
End If
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Function
==== code end =====
2) Put this where you want to run the process
call ftnrunreplace("MyTable","MyField")
Note:
MyTable = replace with your actual target table name
MyField = replace with your actual field name or where the bar code field
name
hth
george
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED]
Behalf Of Joanne Yargeau
Sent: Wednesday, October 18, 2006 6:44 PM
To: [email protected]
Subject: [ms_access] Access 2003, replacing spaces within a field
I am using MS-Access 2003 with ODBC drivers. I am importing a table
from another software. I need to create a parts list that can be bar
coded. I have created the query and the report using the designated
bar code font. The problem is the part number field can contain
spaces randomly within the field as part of the number. My question is
how to replace the spaces within a field with a "," so the bar code
font will be for the whole field and not break the part number into
separate codes and the bar code reader will read the field. I would
appreciate exact syntax as I am fairly new. Any help would be
appreciated. TIA.
Yahoo! Groups Links
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/ms_access/join
(Yahoo! ID required)
<*> To change settings via email:
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
