Solved

Replace function giving type mismatch error

Posted on 2014-03-22
14
1,118 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
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 39

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 119

Expert Comment

by:Rey Obrero
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
 
LVL 39

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 119

Expert Comment

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

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

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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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.
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 …

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now