[AccessD] Modifying a query

Where've you been?  How's that little kid?

Rocky


John Colby wrote:
> 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
>
>   

-- 
Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com