Solved

Replace function giving type mismatch error

Posted on 2014-03-22
14
1,139 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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
 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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 …

777 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