QueryDefs and Openquery

[kevers]

Hi everyone,

I am writing a VBA code to try and modify my query criterion and I'm using QueryDefs and Parameters for that. Unfortunately, I have run into 2 problems:

1) I need to use a conditional criterion, e.g. >date( )-7 and I don't know how to use Parameters for a criterion that is neither a string nor an integer type.

2) Then I use OpenQuery to run my query, but it seems to ignore the parameter change that was done previously using Parameter (I tried with an integer criterion because I didn't know how to do the conditional criterion for this...)

Below is the code that I wrote.

Hopefully that made sense and thank you in advance!

K.

Option Compare Database

Private Sub Command0_Click()
On Error GoTo Command0_Click_Err

Dim QDF As DAO.QueryDef

Set QDF = CurrentDb.QueryDefs("QRY_a")
    
    QDF.Parameters("week_to_query") = ">=Date()-7"
    
    DoCmd.OpenQuery "QRY_a", acViewNormal, acEdit

Command0_Click_Exit:
    Exit Sub

Command0_Click_Err:
    MsgBox Error$
    Resume Command0_Click_Exit

 

[Minty]

I think you are possibly trying to use a sledgehammer to crack a nut here...

You would need to set your current qdf as the recordset to load the query into memory as the recordset. DoCmd.openquery will simply open your stored query, without your parameters set.

If you want to dynamically set the query to dates it might be easier to use a form with some dates on it (FromDate & Todate?) and run the query around those? You could then default those to the last working week but have the flexibility for the user to choose other dates if required?

 

[MarkK]

If that's an action query then you want to use the DAO.QueryDef.Execute() method, not DoCmd.

    ...
    QDF.Parameters("week_to_query") = Date() - 7
    QDF.Execute dbFailOnError
    QDF.Close
    ...

Also, if you show the query text we can offer better help about how to handle the parameters, but passing in the comparison operators is not the way.

 

[kevers]

Hi,
Thank for your replies.
To make things a bit clearer, maybe it's better if I explain what I would like to do ultimately.
I would like to run the query (called QRY_a here) for every week between now and the beginning of this year. As the it works on a weekly basis (looks for the data for a 7 day period only) I will need to loop the query for the number of weeks there are between now and january 1st, where for each iteration, the query parameter changes so that it looks at the following week.
This is why I wanted to find a way to dynamically change the query parameter.

Regarding using openquery, I'll try using recordset and execute.

Thanks for your help.

 

[Minty]

Can we ask what your query will do? It's possible there may be a way to make the query do everything you need without looping?

 

[kevers]

The query itself just looks for data in a table for a 7 day period (I just set the start and end dates of the week). So in itself, it's not complicated but I would like to replace the start and end dates automatically, which I am struggling with.

Would it possible to use the Replace function, but for conditions? (a bit like the post by JANR in this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=215105).

 

[MarkK]

Also, if you show the query text we can offer better help . . .

We're all shooting in the dark here. You should show your query text and your table structure. Also, check out the DatePart() function, which, when used with an interval of "ww", returns the week number.

SELECT DatePart('ww', [YourDate]) As WeekNumber, Sum(ImportantData) as WeeklyImportantSum
FROM YourTable
WHERE YourDate >= #1/1/14# AND YourDate <= Date()
GROUP BY DatePart('ww', [YourDate])

Modify your query so it works like that and you won't need a loop, and your weekly summaries will all be there, one per row, rather than one for each loop of manually replaced start and end dates.
Hope this helps,

 

[kevers]

Here's a simplified version of the query (without all the names).

SELECT TableA.[U##SERIAL], TableB.DEF_DATE, TableB.MER, TableC.DEF_CAT, TableD.[U##DEFER_ID], TableE.TYPE, TableE.DEF_ACT, TableD.[U##WO], TableE.WRK_DNE, TableE.DESC, TableA.[U##STATUS], TableD.CLOSE_DATE INTO [Destin_table]
FROM (TableB INNER JOIN TableD ON TableD.[U##WO] = TableB.[U##CWO_NO]) INNER JOIN TableE ON TableD.[U##WO] = TableE.[U##CWO_NO]
WHERE (((TableB.DEF_DATE)>=Date()-7) AND criterion_A AND criterion_B AND criterion_C AND criterion_D AND criterion_E AND criterion_F AND criterion_G
ORDER BY TableA.[U##SERIAL];

As you can see, it takes different information (text, dates, integers) from different tables, only for the cases that have happened in the past week (among other criteria, which are irrelevant here).

I understand why the DatePart function could be useful here, but I will try to modify it so that it fits.

Thanks again!

 

[MarkK]

Here's code from a recent project of mine that adds a record to a table. You can use this as an example of how to use parameters in an action query . . .

Private Const SQL_INSERT As String = _
    "INSERT INTO tOrder " & _
        "( CustomerID, [Number] ) " & _
    "VALUES " & _
        "( p0, p1 )"
        
Function AddNew(CustomerID As Long) As cOrder
    Set m_data = Nothing
    With CurrentDb
        With .CreateQueryDef("", SQL_INSERT)
            .Parameters("p0") = CustomerID
            .Parameters("p1") = GetNextNumber
            .Execute
            .Close
        End With
        m_id = .OpenRecordset("SELECT @@IDentity").Fields(0)
    End With
    Set AddNew = Me
    
End Function

Some things aren't shown, like the GetNextNumber() function, but see how you can define a parameter in your sql, in this case see "p0" and "p1". Then you can assign values to those parameters in the querydef, and then run the .Execute method of the that querydef. When you use parameters in this way you don't have to worry about delimiters, which are handled by the query. And you could repeatedly call such a querydef inside a loop, passing in parameters as they change for each iteration of the loop.

Hope this helps,

 

[kevers]

Is there no way to replace conditional parameters? I seem to be only able to replace strings, integers and standalone dates.

eg.

QDF.Parameters(>=Date()-7) = >=Date()-8

Because it seems like that is the only major problem I'm having at the moment.


Thanks

 

[MarkK]

your comparison operators are part of the query, so if this is your SQL . . .

SELECT TableA.[U##SERIAL], TableB.DEF_DATE, TableB.MER, TableC.DEF_CAT, TableD.[U##DEFER_ID], TableE.TYPE, TableE.DEF_ACT, TableD.[U##WO], TableE.WRK_DNE, TableE.DESC, TableA.[U##STATUS], TableD.CLOSE_DATE INTO [Destin_table]
FROM (TableB INNER JOIN TableD ON TableD.[U##WO] = TableB.[U##CWO_NO]) INNER JOIN TableE ON TableD.[U##WO] = TableE.[U##CWO_NO]
WHERE (((TableB.DEF_DATE)>=p0) AND criterion_A AND criterion_B AND criterion_C AND criterion_D AND criterion_E AND criterion_F AND criterion_G
ORDER BY TableA.[U##SERIAL];

. . . then see in red I've removed the hardcoded date, and inserted the name of a parameter.

So now we can use that SQL . . .

dim sql as string
dim qdf as dao.querydef
dim rst as dao.recordset
dim d1 as date
sql = "SELECT TableA.[U##SERIAL], TableB.DEF_DATE, TableB.MER, TableC.DEF_CAT, TableD.[U##DEFER_ID], TableE.TYPE, TableE.DEF_ACT, TableD.[U##WO], TableE.WRK_DNE, TableE.DESC, TableA.[U##STATUS], TableD.CLOSE_DATE INTO [Destin_table]
FROM (TableB INNER JOIN TableD ON TableD.[U##WO] = TableB.[U##CWO_NO]) INNER JOIN TableE ON TableD.[U##WO] = TableE.[U##CWO_NO]
WHERE (((TableB.DEF_DATE)>=[COLOR="DarkRed"][B]p0[/B][/COLOR]) AND criterion_A AND criterion_B AND criterion_C AND criterion_D AND criterion_E AND criterion_F AND criterion_G
ORDER BY TableA.[U##SERIAL];"

d1 = date()

with currentdb.createquerydef("", sql)

   do while d1 > #1/1/14#
      .Parameters("p0") = [COLOR="Blue"]d1[/COLOR]
      set rst = .OpenRecordset
[COLOR="Green"]      'do something with this subset of data[/COLOR]
      d1 = d1 - 7
   loop
   .close
end with

. . . and you can see in blue where the actual date is pushed into the query. Now you just have to add another parameter to the query, because to return a week of data, you need a start data and an end date that are 7 days apart. This query on receives one data parameter.

Making sense?

 

[gemma-the-husky]

another way, maybe simpler is to design the query with a "parameter" reference in the "where" row,

then simply set this, and run the query without needing the parameters.

---
eg, a form reference
forms!someform!somefield

or a function to return the value you want
=getsomevalue()

 

[kevers]

Yes, I modified the "where" section of the SQL code instead of using Parameters, and it works well now!

Thanks a lot for all the help!

K.