Solved Replace WHERE clause

[Kayleigh]

I'm looking to iterate through many similar queries and replace the WHERE clause, however I can't find a simple method to retrieve the last line of the query. Couldn't get this method to work. Any suggestions?

My queries look something like this:

SELECT tblOrders.fldOrderID " & vbCrLf & _
"FROM lkpqryAddress1 INNER JOIN tblOrders ON lkpqryAddress1.fldAddressID = tblOrders.fldOAddressID " & vbCrLf & _
"WHERE (((tblOrders.fldOStatusID)=13) AND ((tblOrders.fldOSupplyFitID)=2) AND ((lkpqryAddress1.fldCTradeRetailID)=2));"

And I need to change WHERE clause to:

WHERE (((lkpqryAddress1.fldCTradeRetailID)=2) AND ((tblOrders.fldOSupplyFitID)=2) AND ((tblOrders.fldOStatusID)=13) AND ((tblOrders.fldOStageCatID)<>3));

 

[arnelgp]

copy the code from the module.
use:

ReplaceWhereClause("theOrigSQL","New Criteria"[without the word 'Where'])

 

[CJ_London]

you can always use the mid and replace functions

newsql=replace(oldsql,mid(oldsql,”where”),newcriteria)

but will only work for queries that only have the one criteria and it is the last line (so not aggregate queries for example)

 

[Kayleigh]

Yes that's what I was trying to do.
Tried solution from @arnelgp and it is great