Solved

Replace function giving type mismatch error

Posted on 2014-03-22
14
1,206 Views
Last Modified: 2014-03-22
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
Comment
Question by:HenryV1955
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 40

Expert Comment

by:als315
ID: 39947475
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
 

Author Comment

by:HenryV1955
ID: 39947497
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39947516
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 40

Assisted Solution

by:als315
als315 earned 334 total points
ID: 39947534
Test sample
DBReplace.accdb
0
 

Author Comment

by:HenryV1955
ID: 39947649
Hi Rey

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

H
0
 

Author Comment

by:HenryV1955
ID: 39947660
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
 
LVL 85
ID: 39947677
Possibly you have different Field Types (i.e. a Text field instead of a Numeric field)?
0
 

Author Comment

by:HenryV1955
ID: 39947683
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39947695
in what event are you calling the codes?

can you upload a copy of the db?
0
 
LVL 40

Accepted Solution

by:
als315 earned 334 total points
ID: 39947710
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 166 total points
ID: 39947716
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
 
LVL 85
ID: 39947737
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
 

Author Comment

by:HenryV1955
ID: 39947785
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
 

Author Closing Comment

by:HenryV1955
ID: 39947790
HI

I assigned points based on the closest solutions

Thx
H
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question