Problem with Replace command

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
MDW said:
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)?
Click to expand...

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.
 
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>



Jedster said:
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
Click to expand...
has since been refusing stubbornly. For reference, I will now list the rest
of my code:
 
Back
Top