Solved

Replace function giving type mismatch error

Posted on 2014-03-22
14
1,182 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
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!

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

Industry Leaders: 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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 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