Find and Replace Quote

[tdrBates]

I'm trying to replace the quotes in the field below with nothing.
I'm using MS Access 2000

tStamp,bus,route,run
'2005-01-04 06:18:06.000',7412,331,33101

I get error '3144' - Syntax error in update statement below.
Can someone help me correct this?

Public Function FindAndReplace(ByVal strInString As String, _
strFindString As String, _
strReplaceString As String) As String

Dim intPtr As Integer

If Len(strFindString) > 0 Then
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

Public Function ReplaceQuote()
Dim strSQL As String

strSQL = "UPDATE CardStatus" _
& " SET tStamp = FindAndReplace([tStamp]," '","")"

Debug.Print "strSQL = " & strSQL

DoCmd.RunSQL strSQL

End Function

Thanks for any assistance!

 

[traingamer]

Instead of all of that, why not:

tStamp = Replace(tStamp, "'", "")

traingamer

 

[tdrBates]

I used the replace command against the table in 'DataSheet view', but it only allows me to replace a few thousand records at a time.

I'm trying to eliminate quotes in about 100,000 records

When you say:

CardStatus.tStamp = Replace(tStamp, "'", "")

How would this code recognize the table I'm trying to update?

 

[traingamer]

I just meant use Replace rather than the complicated function you have with looping and InStr, etc.

Yours would probably work with

strSQL = "UPDATE CardStatus" _
    & " SET tStamp = [COLOR=red]'" & [/color]FindAndReplace([tStamp]," '","")[COLOR=red] & "'[/color]"

traingamer

 

[tdrBates]

Now I get the error "External name not defined"

It appears that the Code does not recognize [tStamp] field inside function

strSQL = "UPDATE CardStatus" _
& " SET tStamp = '" & FindAndReplace([tStamp]," '","") & "'"

 

[tdrBates]

This is wierd.

Do I need to set some type of reference, so that MS Access 2000 recognizes table fields within functions?

Both the replace and the FindAndReplace give me the External name not defined error.

 

[traingamer]

Sorry again.
Try:

UPDATE CardStatus SET  tStamp = Replace([tstamp],"'","");

is all the code you need. If you are doing it from a function, you need to double the quotes like

strSQL = 
"UPDATE CardStatus SET  tStamp = Replace([tstamp],""'"","""")"

traingamer

 

[RoyVidar]

I'm not sure Access/Jet likes the replace function within a query, I'm not to sure about passing the litterals either, but perhaps the following alterations?

[tt]Public Function FindAndReplace(ByVal strInString As String) As String
FindAndReplace = replace(strinstring,"'","")
End Function

Public Function ReplaceQuote()
Dim strSQL As String
strSQL = "UPDATE CardStatus" _
& " SET tStamp = FindAndReplace([tStamp])"
Debug.Print "strSQL = " & strSQL
DoCmd.RunSQL strSQL
End Function[/tt]

Roy-Vidar

 

[traingamer]

I only tested in a query, not from a function.

The key is the PUBLIC function, as Roy-Vidar points out.

traingamer

 

[GingerR]

Hey there--I've used it in a query. Seems you're making this more complicated than it needs to be? Like traingamer, I just put a calculated control in a query and

=REPLACE([Blah],"'","")

works just fine....

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

In the above scenario, I think the following will fail

[tt]strSQL = "UPDATE CardStatus" _
& " SET tStamp = '" & FindAndReplace([tStamp],"'","") & "'"[/tt]

as will probably

[tt]strSQL = "UPDATE CardStatus" _
& " SET tStamp = '" & replace([tStamp],"'","") & "'"[/tt]

because here the field is retreived as if it was a form control, and from how I read it, this is called from the query, so VBA does not know what [tStamp] is.

I think the following will fail

[tt]strSQL = "UPDATE CardStatus" _
& " SET tStamp = FindAndReplace([tStamp]," '","")"[/tt]

because of the quotes within the sql string doesn't match up.

Using doublequoting

[tt]strSQL = "UPDATE CardStatus" _
& " SET tStamp = FindAndReplace([tStamp],""'"","""")"[/tt]

with my replace function, prior to removing the string to search and string to replace with, which matches the quotes, I get a type conversion error on fields with Null, works on other fields. Passing variants in stead of strings (se bottom) works. Then my suspicion vs passsing the litterals ("'", "") where not correct.

Using

[tt]strSQL = "UPDATE CardStatus" _
& " SET tStamp = FindAndReplace([tStamp])"[/tt]

with my "shortened" function does also work, but isn't needed.

On my xp (2002), this works too

[tt]strSQL = "UPDATE CardStatus" _
& " SET tStamp = replace([tStamp],""'"","""")"[/tt]

but my 2000 is a bit reluctant to the replace function within a query, claims "3085 Undefined function 'replace' in expression".

So I think to make this work in 2000, either my above sample should work, or something like the below, here also trying to take Null into account.

[tt]Public Function FindAndReplace(ByVal strInString As variant _
byval strFind as string, byval strReplace) As Variant
if trim$(strinstring & "")<>"" then
FindAndReplace = replace(strinstring,strfind,strreplace)
else
FindAndReplace=Null
end if
End Function[/tt]

I also had a supsicion the commas within the field might cause problems, but it doesn't seem like it.

One question though, this exercise, what is the purpose of it - from what I read here, it's about storing more than one value in a field, i e violating normal forms, and creating future headaches when trying to retrieve meaningful information from it. Why not separate it into multiple fields?

Roy-Vidar

 

[tdrBates]

You were right, I was making this more difficult than it needed to be. I used the Replace command in a query instead of a function and this works great.

My objective is to convert the "tStamp" field from a text datatype to a date/time datatype.

I want the "tStamp" field ('2005-01-04 06:18:06.000') to look like this (2005-01-04 06:18:06)

I used the replace command in the query grid to replace the apostrophes, but would like to replace both apostrophes and the .000 at the end of the field at the same time.

Any suggestions on how to replace both apostrophes and the .000 at the same time?

Thanks again for all your help!!! You guys are a great resource!!! I mailed in my donation today.

 

[tdrBates]

I nested the Replace function and it works great!!!
Thanks.