Solved Replace WHERE with AND

[ClaraBarton]

I have a form that uses one filter and a subform on that form that uses another. I have an intermediate dialog that pulls the Source from the form and the filter from the subform. I want those selected records copied to another table. When I try to create a SQL with both filters I end up with this:

SELECT * FROM qryContacts WHERE (tblSales.SalesID)>0;  WHERE ([Ck] Not In (0))

I've tried to use the replace function: Replace (sSource, "; WHERE", "AND") but apparently I don't know how to use the replace function.

 

[MajP]

Something like?

Dim strSql as string
StrSql = replace(me.rowsource,";","") 'get rid of semicolon
StrSql = strSql & " WHERE " & me.Filter & " AND " Me.subformName.form.filter ' add the 2 filters

 

[ClaraBarton]

Thank you for your help. Here's what seems to work:

Dim strSql2 As String
strSql2 = Replace(sSource, ";", "") 'get rid of semicolon
Dim strWhere2 As String
strWhere2 = Replace(sFilter, "WHERE", "AND")
strSql2 = strSql2 & strWhere2

 

[isladogs]

Surely that will replace both instances of WHERE with AND...

 

[Saphirah]

For what it looks like you do have 2 spaces in your SQL string after the Semicolon. So if you try to replace "; WHERE" with one Space it won't find the string.
Try:

Replace (sSource, ";  WHERE", " AND")

 

[CJ_London]

would help to know what you are actually trying to do. If it is to add another criteria then you can use something like this

strSQLOrig="SELECT * FROM qryContacts WHERE (tblSales.SalesID)>0;"
strSQLNew="SELECT * FROM (" & replace(strSQLOrig,";","") & ") WHERE [Ck] Not In (0)"