I've been trying to use the Replace function in my VBA module in Access 2010 but keep getting type mismatch errors. Heres a sample of my code
Dim varfind As String, varReplace As String, varSQL As String, varNewSQL As String
varfind = Me.Find.Value 'value is 'Field1'
varReplace = Me.Replace.Value 'value is 'Field1New'
varSQL = "SELECT tblOne.Field1 FROM tblOne"
' varNewSQL = Replace(varSQL, varfind, varReplace, , , vbTextCompare) 'gives syntax error
Interesting that even though start and count are optional, looks like if you have to fill them in if you enter a value for compare at the end otherwise you get a syntax error above
....so filling them in like this....in each case the default value
varNewSQL = Replace(varSQL, varfind, varReplace, 1, -1, vbTextCompare) 'give type mismatch error
I tried changing vbTextCompare to vbBinaryCompare same error
So whats up with this function?