Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    34

    Question Unanswered: Manipulating stored queries in code?

    Hi, this question has been bothering me for a while, and so I hope someone might be able to offer some advice...

    I have a number of queries which retrieve and manipulate data using implicit relationships between data e.g. by using joins. However, I want to further narrow the results returned to only those satisfying where clauses, e.g. only those fo ra specific customer. I was wondering if it was possible to specify the where clause of a saved query in code?

  2. #2
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    Yes, you can. Try something like this:

    Dim qdf as DAO.querydef
    Set qdf = CurrentDb.QueryDefs("Query_Name")

    with qdf.sql you can retrieve/alter/reset the sql-text
    A good developer is a lazy developer - don't be to lazy, share your knowledge

  3. #3
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    I don't know about just modifying the WHERE clause. I suppose you could read the query in as a qdf and parse the qdf.SQL part of it. kinda like below

    Code:
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    
    strSQL = "SELECT " & strSource & ".*, " & strDates
    
    'open stored query
    Set qdf = CurrentDb.QueryDefs("qryDates")
    
    'change the SQL string. here is where you could insert code to replace 
    'everything after the WHERE part. then you would
    qdf.SQL = strSQL
    
    'opens the query in design mode to change it
    DoCmd.OpenQuery "qryDates", acViewPreview, acEdit
    'closes query
    DoCmd.Close acQuery, "qryDates", acSaveYes
    hope it helps you out some. good luck.

  4. #4
    Join Date
    Jul 2004
    Posts
    34

    Talking Specifying Query Parameters in VBA

    Found a very useful page

    http://www.a1vbcode.com/vbtip.asp?ID=28

    which tells you how to do exactly what I want, check it out!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •