Strip out garbage character from text fields

[alexisb]

I created a Word form with formfields from which data is loaded into Access through VB. The load works fine except that the text form fields are loading one garbage char (ASC13) at the end of non-null text loaded or full of garbage if text is blank from Word.

I have written VB code in Access to strip out the garbage after the load is done. I do two separate function calls; one to look for ASC13 as the last char and another to look for the entire value being garbage.

My code looks at each field in each table and it is a lot of VB code, even with using function calls, and not very efficient. But it does work.

Does anyone know a better way to handle stripping out the garbage characters? If I could fix the data as it is being loaded, that would be really great, like I use nz and trim as data is loaded. I have reviewed past posts and tried the replace function but couldn't "identify" the garbage character to get the replace to work.

Thanks.
Alexis

 

[Frink]

Hallo,

The constant vbCR is the ANSI char 13. You should be able to use that in the replace function you found.

- Frink

 

[alexisb]

Thanks, Frink. I tried this logic with replace but nothing got replaced. There were no errors and the update said it was updating 4 rows, which was correct, but the values didn't change. Here's my code:

Public Function ReplaceGarbage()
Dim strSQL As String
strSQL = "UPDATE tblServiceHistory_refill_summary_multi_media SET other1= FindAndReplace([other1])"
DoCmd.RunSQL strSQL
End Function
Public Function FindAndReplace(ByVal strInString As String) As String
FindAndReplace = Replace(strInString, vbCr, " ")
End Function

One other note, these garbage characters only happen on my PC. When I installed the programs on my customer's PC, it was fine and no garbage characters were created. Any ideas?
Thanks so much.
Alexis

 

[Frink]

Hallo,

Your code looks ok. Are you sure the garbage is just vbCr? If could be vbLF as well, or any other char.

Try stopping the code and looking at the ANSI values of the chars in the string.

- Frink

 

[alexisb]

I used the ASC command,which returned a 13. Plus, all my logic to check the last character is based on ASC13 and the replace is done correctly. But my code is tedious because it has to check each field one by one. I hoped to find a less complicated way to make the fix, and hopefully at the time of the load. I'll paste a sample of that code below.

Do While Not rstRptDist.EOF
If Asc(Right((rstRptDist!name), 1)) = 13 Then
Call FixGarbageAtEnd("tblServiceHistory_rept_distrib", "name", rstRptDist!report_id, rstRptDist!line_number)
End If
If Asc(Right((rstRptDist!phone), 1)) = 13 Then
Call FixGarbageAtEnd("tblServiceHistory_rept_distrib", "phone", rstRptDist!report_id, rstRptDist!line_number)
End If
rstRptDist.MoveNext
Loop

Public Function FixGarbageAtEnd(strtablename As String, strfieldname As String, ireportID As Integer, ilinenum As Integer)
Dim strSQL As String
strSQL = "UPDATE " & strtablename & " SET " & strfieldname & _
" = mid([" & strfieldname & "],1,(len([" & strfieldname & "]) - 1)) WHERE report_id = " & ireportID & " and line_number = " & ilinenum
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Function


Also, this problem with the garbage did not happen in the load on my customer's machine, which is very strange to me.

Thanks,
Alexis

 

[Frink]

Hallo,

What code do you use to load the fields into Access?

- Frink

 

[alexisb]

Here's one section from the code.
'Go to next table (2) in Word - Report Distribution (tblServiceHistory_rept_distrib)
'Techs can add rows so need to loop based on how many rows in the table after row 3
gappWord.Selection.MoveRight UNIT:=wdCharacter, Count:=1
gappWord.Selection.GoTo What:=wdGoToTable, Which:=wdGoToNext, _
Count:=1, name:=""
lngStartRows = gappWord.Selection.Information(wdMaximumNumberOfRows)
'move down 3 and select that cell to start the data load
gappWord.Selection.MoveDown UNIT:=wdLine, Count:=3
gappWord.Selection.MoveRight UNIT:=wdCell
gappWord.Selection.MoveLeft UNIT:=wdCell 'we should be in row 3 now

offset = 3 'start in row 4 which is first row with data
Do While lngRows < lngStartRows
With rstRptDist
.AddNew
lngRows = gappWord.Selection.Information(wdStartOfRangeRowNumber)
![report_id] = lngRptID
![line_number] = (lngRows - offset)
![name] = Nz(Trim(gappWord.Selection.Text), "")
gappWord.Selection.MoveRight UNIT:=wdCell
![phone] = Nz(Trim(gappWord.Selection.Text), "")
gappWord.Selection.MoveRight UNIT:=wdCell
![extension] = Nz(Trim(gappWord.Selection.Text), "")
gappWord.Selection.MoveRight UNIT:=wdCell
! = Nz(Trim(gappWord.Selection.Text), "")
.Update 'update tblServiceHistory_rept_distrib
gappWord.Selection.MoveRight UNIT:=wdCell
gappWord.Selection.MoveRight UNIT:=wdCell
End With
Loop

Thanks!
Alexis

 

[Frink]

Hallo,

Why not replace the calls to

Nz(Trim(gappWord.Selection.Text), "")

with

strGetSelection(gappWord.Selection.Text)

then write

Private Function strGetSelection(byval pvarText as Variant) as string

to do all the Trim, Null conversion and calls to Replace whatever with whatever?

- Frink

 

[alexisb]

Frink,
Great idea, thanks. I made the change you suggested on two of the fields so I could test it. To my surprise, the load did not show any garbage characters even though I had only changed the code for two fields. I removed the logic for those two fields and ran the logic. Again, no garbage characters. I am baffled. What could have changed that stopped the garbage characters from appearing? I did nothing different..
Thanks,
Alexis