Problem with Replace command

[Guest]

I am receiving the error message "Invalid procedure call or argument" when I run the following command:

strSQL = Replace(strSQL, " FROM ", " INTO " & strTableName & " FROM ")

This line is meant to amend the following piece of SQL text in the string variable:

"SELECT s.SalesID FROM tblSales s WHERE s.fkCompanyID = 10"

Can anyone offer any assistance? Oddly, this line was working earlier but has since been refusing stubbornly. For reference, I will now list the rest of my code:

Function BuildResultsTable(strSQL As String, _
strTableName As String, _
lngRecordsAffected As Long) As Boolean

Dim db As Database
Dim qdfAction As QueryDef

Set db = CurrentDb

On Error Resume Next
db.TableDefs.Delete strTableName
On Error GoTo 0
strSQL = Replace(strSQL, " FROM ", " INTO " & strTableName & " FROM ")
Set qdfAction = db.CreateQueryDef("", strSQL)
qdfAction.Execute dbFailOnError
qdfAction.Close

BuildResultsTable = True

End Function


TIA.

 

[Dirk Goldgar]

Did you move to a different version of Access? I'm pretty sure that
Access 97's VBA doesn't include the Replace() function. If you're in
2k or newer, do you have a reference to Visual Basic for Applications
(Tools > References)?

And while you're in the References dialog checking that, look for any
other reference marked as MISSING. If you find one, note down what it
was, un-check it, close the dialog, then reopen it, locate the reference
in the list (it will probably have moved), and check it again.

 

[Ken Snell]

Assuming that this is not a References error (which it usually isn't), the
problem with Replace is a bug that is caused by ACCESS improperly
interpreting the sixth argument (which you likely aren't specifying, as it's
optional) as a language setting and not a comparison mode. The way to avoid
this error is to always specify all six arguments of the Replace function,
even the optional ones:

MyResult = Replace("SourceString", "FindString", "ReplaceString", 1, -1,
vbTextCompare)

--

Ken Snell
<MS ACCESS MVP>

I am receiving the error message "Invalid procedure call or argument" when I run the following command:

strSQL = Replace(strSQL, " FROM ", " INTO " & strTableName & " FROM ")

This line is meant to amend the following piece of SQL text in the string variable:

"SELECT s.SalesID FROM tblSales s WHERE s.fkCompanyID = 10"

Can anyone offer any assistance? Oddly, this line was working earlier but

has since been refusing stubbornly. For reference, I will now list the rest
of my code: