Solved Replace WHERE clause (1 Viewer)

Kayleigh

Member
Local time
Today, 19:20
Joined
Sep 24, 2020
Messages
706
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:
Code:
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:
SQL:
WHERE (((lkpqryAddress1.fldCTradeRetailID)=2) AND ((tblOrders.fldOSupplyFitID)=2) AND ((tblOrders.fldOStatusID)=13) AND ((tblOrders.fldOStageCatID)<>3));
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:20
Joined
May 7, 2009
Messages
19,169
copy the code from the module.
use:

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

Attachments

  • SQLCriteria.accdb
    356 KB · Views: 294
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 19:20
Joined
Feb 19, 2013
Messages
16,553
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)
 

Users who are viewing this thread

Top Bottom