Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2009
    Posts
    120

    Unanswered: Date Prompt Multiple Query

    Hi,
    I am not sure if this is possible but I figured I would ask the question.

    I currently have 17 different queries built to gather different data from the same table. Each Query has a Date Prompt in it but on a different Date field. Is there a way to carry the Date from the First Query to each subsequent query without having to re enter the date over and over again as the Begining and Ending dates will not change from query to query.

    This is not done in SQL Don't know SQL would need a solution for regular query building in Access.

    Thank you.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A solution would consist in using parameters queries and call them from a VBA procedure, like this:
    1. The queries are built on the model:
    Code:
    PARAMETERS P0 DATETIME; SELECT ... FROM ... WHERE ... = P0;
    Note: SELECT could be replaced by DELETE, INSERT or UPDATE.

    2. You call them from a VBA procedure, like this:
    Code:
    Const c_QueryNames As String = "Query1,Query2,Query3..., QueryX"
    
    Dim qdf As DAO.Recordset
    Dim rst As DAO.Recordset
    Dim var As Variant
    Dim i As Long
    
    var = Split(c_QueryNames, ",")  ' Or any other way to retrieve the name of the queries.
    For i = 0 To Ubound(var)
        Set qdf = CurrentDb.QueryDefs(var(i))
        qdf.Parameters(0) = <DateValue>
        Set rst = qdf.Openrecordset ' Case of a SELECT query.
        qdf.Execute                 ' Case of a DELETE, INSERT or UPDATE query.
        qdf.Close
    Next i
    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    120
    Thank you Sinndho, that looks very impressive however I have no idea what any of that means. My queries are not built in VBA, they are built using the standard Access Query Designer.
    I would need a solution for that rather than VBA.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Sinndho View Post
    The queries are built on the model:
    Code:
    PARAMETERS P0 DATETIME; SELECT ... FROM ... WHERE ... = P0;
    These are "regular" queries, except that they declare a parameter. You can open your existing queries in the query builder (make copies), switch to SQL view then edit them to add the parameter declaration and replace the non-declared parameter (probable in the form of: [Please enter the Date] by the declared one.
    Quote Originally Posted by CHI Brian View Post
    I would need a solution for that rather than VBA.
    You cannot expect to achieve some goal that Access standard interface does not allow without writing (and using) any VBA code.
    Have a nice day!

  5. #5
    Join Date
    Mar 2009
    Posts
    120
    Thank you Sinndho, however I don't know how to do all the VBA stuff. I am not a true programer. Self taught and well I only know how to use the basic stuff in Access, so the VBA is a bit over my head.

    Is there something simpler That i might be able to do just in the standar design view?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by CHI Brian View Post
    Is there something simpler That i might be able to do just in the standar design view?
    Not as far as I know. As I wrote before, everything that Access does not provide in one of its "standard" interfaces must be programmed.
    Have a nice day!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    What you could do is create a form that acts as your report switchboard (menu)
    on that form place a control for each report (use the button wizard to open a report) AND a control to contain your date.
    take note of what you call that form AND that date control

    then you need to modify the openreport macro to specify a WHERE clause for each reports command button
    Code:
    Private Sub mycommandbutton_Click()
    Dim strWhereClause As String
    strWhereClause = "mydatecolumn = #" & Format(mydatecontrol "yyyy/mm/dd") & "#"
    DoCmd.OpenReport "myreportname", acViewPreview, , strWhereClause
    End Sub

    change
    mydatecolumn for the name of the required date column in each report
    mydatecontrol for the name of the control holding the date on the form
    myreportname for the name of the report in question
    mycommand button shoudl be repalced with whatever you have called the control and should appear automatically when you view the code if you used the wizard. if not then right cliock each command button | build event | build code

    does this mean you have to dabble in VBA, well yes. but not significantly and I cant see an easier way of doing this, short of modifying each report and applying as filter in the report on open event, which will also work, however it makes the code even more fragile. the above is reasonable

    however this code is fragile, you can make it a bit more robust if you set the format of mydatecontrol to be a valid date
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2009
    Posts
    120
    Thanks for this. NOt exactly sure what it means though.

    This particular project is on hold, the report the quieries were built to populate has been changed and we don't know what the new one looks like yet.

    I would have a button on a menu to run the report utlimatly based on all these quieres. But it sounds like I would need a secondary form to hold the controls for the date and the somehow using the code transfer it to all the controls for each query in turn.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or store the date on form, and call that value from the query
    or set a filter as part of the reports on open arguments
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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