Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1341
  • Last Modified:

Replace function giving type mismatch error

Hi all
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

MsgBox varNewSQL

I tried changing vbTextCompare to vbBinaryCompare same error

So whats up with this function?

Thanks

H
0
HenryV1955
Asked:
HenryV1955
  • 6
  • 3
  • 3
  • +1
3 Solutions
 
als315Commented:
It is very bad practice to use reserved words as field name.
Rename fields on your form to Find1 and Replace1 and try again
0
 
HenryV1955Author Commented:
Yes I agree, I just threw those in there for this example.

Doesn't matter what I choose for find and replace or what my sql string is, I still get the error
That's what the issue is

Thx
0
 
Rey Obrero (Capricorn1)Commented:
use the test if the string varFind is in the Sql

if instr(varSQL, varFind) then
   
   varNewSQL = Replace(varSQL, varfind, varReplace)

end if


try also
DEBUG>Compile
correct any errors raised
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
als315Commented:
Test sample
DBReplace.accdb
0
 
HenryV1955Author Commented:
Hi Rey

Nope sorry, type mismatch error on the replace line. I always debug compile when I change my code

H
0
 
HenryV1955Author Commented:
HI als315

Thanks for you db

Interesting, I tried your db , works fine. I copied your code into my db, making sure the text boxes are correctly named and I get the type mismatch error

Only difference I can see is, I also have Option Explicit on my forms for variable declaration.

Is there some other global setting I'm missing?

H
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Possibly you have different Field Types (i.e. a Text field instead of a Numeric field)?
0
 
HenryV1955Author Commented:
Nope, text boxes all round.

Other difference als db is using unbound text boxes with a default value. I'm using bound text boxes to a table.

Both are result boxes are unbound text boxes so it should accept the value, except errs on the replace line, before it assigned to the result box
0
 
Rey Obrero (Capricorn1)Commented:
in what event are you calling the codes?

can you upload a copy of the db?
0
 
als315Commented:
Try to rename field in my sample from Replace1 to Replace and you will have mismatch error. May be some field on your form has this name?
0
 
Rey Obrero (Capricorn1)Commented:
change the name of your textboxes

"Find"  to txtFind

and

"Replace to "txtReplace"


If i am not mistaken these two "Find" and "Replace" are reserved words
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Nope, text boxes all round.
I'm not concerned with the Form control, I'm concerned with the Data Type of the underlying Table Field. Are those textboxes based on Text fields, Date Fields, Numeric Fields, etc etc ...
0
 
HenryV1955Author Commented:
Hi all

So I copied als  form into my db, bound it to my table and it worked fine.

I took the same controls and code from his form, put them into my form, and I got a different error.

Compact and repair made no difference.

So I created a new dbm imported both als and my form into it and both worked fine.  Go figure. One of those peculiar access problems you come across now and then. It was likely hanging on to some value even C&R couldn't clear.

Anyway thanks to you fellows for your input. I'll be assigning points shortly
0
 
HenryV1955Author Commented:
HI

I assigned points based on the closest solutions

Thx
H
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now