Change Query with VBA

[jsic1210]

Hello All,

I would like to replace the WHERE clause of a query using VBA, based on the value in another form. In the book I have, it said to do it something like this:

'Change qryNewBusinessYTD
Dim strSQL As String
Dim qdf As QueryDef
Dim db As Database

    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryNewBusinessYTD")
    strSQL = "[Billing Effective Date] <= #" & Me.txtAsOfDate & "#"
    
    'Change Where Clause
    qdf.sql = replacewhereclause(qdf.sql, strSQL)
    Set qdf = Nothing
    Set db = Nothing

When I try to run the code, I get an error saying "Compile Error: Sub or Function not defined." It then highlights the phrase replacewhereclause. I thought this was a command in VBA (the book implies that it is). Does anyone know how to fix this?

 

[pbaldy]

It is not a built-in function. I'm aware of a custom function on Armen Stein's site, which is perhaps where you got it (he is also an author, so it may be in the book as well).

 

[jsic1210]

You are correct; he is one of the authors. I will try to find that function in the code. Do you happen to have a link to his site? I couldn't find it by a Google search.

Thanks for your help!
Jeff

 

[pbaldy]

I've met him; very very bright guy. I think this is the code:

http://www.jstreettech.com/files/basJStreetSQLTools.zip

 

[jsic1210]

Before I try to decipher all that code, are there any other suggestions for how to change the WHERE clause of a query using VBA?

 

[spikepl]

"decipher" ? Put it into a module, read the instructions, and then you have your
replacewhereclause