How to do a global "search and relpace" type function in Access queries

The sub attached will work to an extent, however you should be aware that it will replace all instances of "= -1" and "= 0" with "Yes" and "No" respectively wihtin the query - potentially changing criteria that you don't want changing....

Depending on how many queries you have it may be easier to make the changes manually....

sub changequeryclause(strQueryName as string)
 
dim dbs as database
dim qdf as querydef
dim strSQl as string
 
set dbs = currentdb()
set qdf = dbs.querydefs(strQueryName)
strsql = qdf.sql
 
strSQL = replace(strsql, "= -1", "= 'Yes')
strSQL = replace(strsql, "= 0", "= 'No')
 
qdf.sql = strsql
 
qdf.close
set qdf = nothing
dbs.close
set dbs = nothing
 
end sub

Select allOpen in new window

you could also wrap the procedure in another to interate through all of your queries... BUT you need to be sure that you are ok for all queries containing the syntax "= -1" or "= 0" can be changed....

You may need to use "=-1" and "=0" (no spaces....) in the replace function above - open a copule of queries in sql view to check the format of the where clause...

I'd also strongly recommend making a backup copy before running the procedures...

sub changequeries()
 
dim dbs as database
dim varitem as variant
 
set dbs = currentdb()
 
for each varitem in dbs.querydefs
 
   changequeryclause(varItem.anme)
 
next varitem
 
dbs.clsoe
set dbs = nothing

Select allOpen in new window

@mds-cos

1. I agree with JimHorn about the third party utilities.  Very good expenditure of your $$$$

2. I do not think you should use Yes/No in SQL Server.  Intead, you should use True/False.

3. The translation of MSAccess SQL to TSQL can be quite tricky.  For instance, into what kind of column type are you converting the Access Yes/No columns?  You might need to compare to 1/0 literals.

4. Even Roll-Your-Own SQL changes need consider how/where the literal values are used.
Example:
strSQL = replace(strsql, "= -1 ", "= True")
strSQL = replace(strsql, "= 0 ", "= False")
strSQL = replace(strsql, "=-1 ", "= True")
strSQL = replace(strsql, "=0 ", "= False")
strSQL = replace(strsql, "= -1;", "= True;")
strSQL = replace(strsql, "= 0;", "= False;")
strSQL = replace(strsql, "=-1;", "= True;")
strSQL = replace(strsql, "=0;", "= False;")

5. This is a good opportunity to rewrite some of your queries, taking advantage of the Server-grade database engine's capabilities.  Start with the queries that are run most often or that run the slowest.

mds-cos,

Please consider *Not* changing the Number to Text values.
Obviously, leave Zero, but change the -1's to +1's

Then make a Yes/No table

Table Name: tblYesNo
Fields:
YN_ID, Primary key, Integer
YN_Text, Text, 3 chars

YN_ID    YN_Text
   0           No
   1          Yes

This way you can ues this table for all your Yes/No lookups
It is far easier to refer to variables in VBA code when they are numbers, than if they are text strings.
It is easier to get counts of the Yes's and No's if they are numbers.
Yes/No is typically used for Option groups and checkboxes. Option groups and checkboxes are based on numbers *Not* text. If you use text, you will have to create "Translation code", to get them to work.

Basically it is applying the same database theory as you do in all your other tables.
(You store the number, not the text)

Just my 2c

JeffCoachman

I think the Jet engine translates these boolean values as they come into the PC.  In the MSAccess/VBA world, 0 is False and any non-zero value is True.

aikimark,

<I think the Jet engine translates these boolean values as they come into the PC.  In the MSAccess/VBA world, 0 is False and any non-zero value is True.>
Yeah that's what I thought.
I just was not sure with all the different flavors of and SQL server floating around these days!
;)

My only concern is the confusion for new users over storing the actual text string "Yes" or "False", versus storing the value of 1, or False.
Thanks for the clarification.
:)

JeffCoachman

Yes/No seems to be an MSAccess 'feature' that isn't implemented in other relational databases' SQL.

Avoid string comparisons whenever possible for performance reasons.  The advantage of using a checkbox control is that it minimizes user confusion.

aikimark,

<Yes/No seems to be an MSAccess 'feature' that isn't implemented in other relational databases' SQL.>
Ha! Ha! Ha!!...
I Had to laugh!
:)

See here:
http://allenbrowne.com/NoYesNo.html

Thanks for the insight.
Have a great weekend!
:)

JeffCoachman

Thank you all for your comments.

A random comment about yes/no true/false and Access vs. SQL.  I do find it quite interesting that TRANSACT-SQL does not support a statement like SELECT * FROM employeestatus WHERE inoffice = 'yes'.  The same statement will however work in Access even as a pass-through query.  It was nice of M$ to consider doing at least a bit of translation before "passing through" to the back-end server.

For now I am giong to stick with Yes/No to make it easy for others to follow what I am doing (the data in question represents information that is truely "Yes / No" vs. "True / False").  When this project progresses to performance concernes I will be re-writing a lot of stuff, and certainly developing views and stored procs.  At that point yes/no logic will disappear as everything becomes SQL centric rather than Jet centric.

This is such a huge application (I truely have never seen anybody push Access to the extreeme like this before, and am a bit suprised to see Access handeling it so well) that my first step is to get the thing working with SQL as the back-end datastore.  After that I can start wading in to performance improvement.  As somebody noted, the minute differences between Access and TRANSACT-SQL can be tricky at times -- so it is better not to create a situation where I must figure out if bugs were created by the conversion vs. the performance re-writes.

Thanks again, everybody!

Just to round this out for future folks,  I gave Rick Fisher's 'Find and Replace' utility a whirl since it sounded like a rather useful tool.  It would not install on my system (I'm running Access from the Office XP Developers Edition with most of the Microsoft updates installed on a Windows XP Pro platform).

I Found a similar utility -- and free to boot.  MDBSearch by Ucora (also referred to as "ucora Find and Replace").  This will be a great addition to my bag of tools!

never refund points for abandoned questions.

akimark,

> never refund points for abandoned questions.

Please read this (cleanup guidelines):
https://www.experts-exchange.com/help.jsp#hi131

I posted the PAQ/Refund recommendation, because the asker posted the solution used.  If you still have an objection with that after reading the guidelines I'm using,  let me know (but please be specific).

Thanks,
mbizup
EE cleanup Volunteer

@mbizup

Actually, jimhorn ought to be awarded the points since he suggested the utilities that seemed to get results.