[AccessD] Modifying a query

You forgot the DIM statement:

DIM db as dao.database
DIM qdf as DAO.Querydef
	set db = currentdb
	set qdf = db.querydefs("MyQueryName")
	qdf.SQL = strMySQL
	qdf.close
	Set qdf = nothing
	db.querydefs.refresh

THEN use the query.


John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy Lacey
Sent: Wednesday, June 07, 2006 3:10 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Modifying a query

Rocky, you can do this yourself.

Set qdf=db.QueryDefs("qryxyz")
strSQL=qdf.SQL

Then use instr to identify the WHERE statement within strSQL. Use inStr
again to identify the ORDER BY or GROUP BY if it has one. This allows you to
separate out 3 strings: one with everything which preceds the WHERE, one
with the WHERE construct and one with everything following, let's say
strSQLStart, strSQLWhere and strSQLEnd. Pop your own WHERE statement into
strSQLWhere and then do:

qdf.SQL=strSQLstart & strSQLWhere & strSQLEnd qdf.Close

Job done.

HTH

-- Andy Lacey
http://www.minstersystems.co.uk 

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky 
> Smolin - Beach Access Software
> Sent: 07 June 2006 05:24
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Modifying a query
> 
> 
> Steve (and Darren and Dan):
> 
> That will replace the entire query which is what I'll do if I have to.
> Won't be that hard (I can copy the SQL statement right out of the SQL 
> view into my code).  But it seems to me there should be a way to 
> manipulate queries with the same detail as something like a table 
> where you can modify one property of one field.  But apparently you 
> can't just add or delete a field, or change a single criterion.
> 
> Rocky
> 
> 
> Steve Conklin wrote:
> > Yep, need to edit the query def's SQL property:
> >
> > Set q = Currentdb.querydefs("my_users_query")
> > q.SQL = "my new sql statement"
> > Currentdb.querydefs.refresh
> >
> > Hth
> > Steve
> >  
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky 
> > Smolin - Beach Access Software
> > Sent: Tuesday, June 06, 2006 8:20 PM
> > To: Access Developers discussion and problem solving
> > Subject: [AccessD] Modifying a query
> >
> > Dear List:
> >
> > Can you modify the criteria in an existing query through code?  
> > Usually I just construct the query as a SQL statement and use 
> > db.execute to run it. But this time I think it would be better to 
> > leave the query where it is and modify the criterion based on a 
> > selection by the user.  The query is part of a predefined set of 
> > queries that were embedded in a macro by the user.
> >
> > MTIA,
> >
> > Rocky
> >
> > --
> > Rocky Smolin
> > Beach Access Software
> > 858-259-4334
> > www.e-z-mrp.com
> >
> >
> >   
> 
> --
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com