Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Location
    United Kingdom
    Posts
    56

    Unanswered: Displaying values entered in parameter query on a report

    Hey guys,

    Got a bit of problem that's got me stumped!

    I have a report based on a parameter query that returns records within a range of two dates contained in the field [DateOfMeeting]. I have used the following in the criteria section of the query:
    >=[Type the beginning date:] And <=[Type the ending date:]

    Therefore when the report is run it asks for "the beginning date" and the "ending date". However, is there any way of displaying the values the user enters in these msgboxes on the actual report?

    Or alternatively should I be looking trying to display the earliest date and the latest date generated by the query on the report?

    Thanks for your help.

    Rusty

  2. #2
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    I can set you on the right track. There is someting called a parameter (DAO.Parameter i think). What you need to do is evalutate the paramters in the reports recordsource (query) then parse and print them.


    Here is a start:

    Dim db As DAO.Database
    Dim p As DAO.Parameter


    Set db = CurrentDb
    Set qd = db.QueryDefs("Your Query Name')" query for evaluation here

    For Each p In qd.Parameters
    p.Value = Eval(p.Name)
    Next p

  3. #3
    Join Date
    Sep 2004
    Posts
    1
    An easy way to do this is in the Query create another Column for each parameter and put the Parameter Question there as well. When this runs it will ask the question but replace both values with the answer.

    i.e.
    (Your Criteria statement)
    >=[Type the beginning date:] And <=[Type the ending date:]

    Add these two fields to the Query
    BeginningDate:[Type the beginning date:]
    EndingDate:[Type the ending date:]

    When this runs the BeginningDate field will have what ever was typed, same goes for the ending date. You can now place these fields on the report just like any other query field.

  4. #4
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    You can use a text box. In the part that says Unbound put in something like "For the dates " & [Type the beginning date:] & " through " & [Type the ending date:]

    Why this works: Basically what ever you put in the [] in the query becomes a variable that can be placed into the report.

    Darasen

  5. #5
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    Also, you don't have to say >=. BETWEEN [] AND [] will work because BETWEEN is actually INclusive (strange but true).
    wazz

  6. #6
    Join Date
    Nov 2003
    Posts
    10
    I have the same questions the original poster asked. I have one 'DATE' field which carries dates going back for two years. I want to run report filtered for a given date range.

    What I have done in the past was to have between[begin date:]and[end date:] in the DATE field criteria. This works, but now I'd like the input data for these two parameters to show up on a report.

    I added two new columns labeled
    BeginDate:[Type the Begin Date:]
    EndDate:[Type the End Date:]

    But how do I filter the record set of the query for the data entered in the new columns with respect to the DATE field.

    Would there be some 'where' clause I need to enter in the criteria field for DATE that is passed from BeginDate and EndDate ???

    Thanks
    Don

  7. #7
    Join Date
    Nov 2003
    Posts
    10
    I figured it out

    The new column fields labeled:
    BeginDate:[Type beginning date:]
    EndDate:[Type ending date:]

    the text in bold become the criteria in the DATE field

    >=[Type beginning date:] AND <=[Tyep ending date:]

    Very clever...Thanks

  8. #8
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    There is no need for any extra columns. Put your dynamic parameters([Begin Date:] and [End Date:]) in the criteria for your Date field like you did, but use Between like Wazz said because it is cleaner than all of the >= and <= nonsense. Then do as Darasen said, and use those exact same dynamic parameters in unbound boxes in the header on your report. The extra columns in your query are unnecessary clutter.

    TD

  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    [QUOTE=Rusty's Way

    Hey Rusty,
    Here is the real simple for what you want as this is what I have.
    For the Parameter part of your Query do this:

    [b]Between[EnterBeginningDate] And[EnterEndingDate] [/b]

    Next to show the dates input on the report do this:

    ="From " & [EnterBeginningDate] & " Through " & [EnterEndingDate]

    Yet the simplest way that I really like is to create a DialogForm to let the user input the dates and then open the report as such:
    Create an UnboundForm.
    Place 2 Unbound TextBoxes on the form.
    Place 2 CommandButtons on the form.(OpenReport) and (Reset)
    For the Reset CommandButton put this code behind it:
    Code:
    Private Sub Reset_Click()
    
        If Not IsNull(BeginningDate) And Not IsNull(EndingDate) Then
        
        [BeginningDate] = ""
        [EndingDate] = ""
        
        End If
    End Sub
    What this does is just in case the user input the incorrect dates, all they do is Click that button and the TextBoxes are cleared. Keeps from highlighting again and hitting delete.

    Now for the OpenReport CommandButton do this:
    Code:
    Private Sub cmdOpenForm_Click()
    On Error GoTo Err_cmdOpenForm_Click
    'A sample by Bud to make the form pull up data on both dates.
    
    
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "frmDateGrabber"
        
        stLinkCriteria = "[PurchaseDate]>=" & "#" & Me![BeginningDate] & "# AND [PurchaseDate]<=" & "#" & Me![EndingDate] & "#"
    
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Exit_cmdOpenForm_Click:
        Exit Sub
    
    Err_cmdOpenForm_Click:
        MsgBox Err.Description
        Resume Exit_cmdOpenForm_Click
        
    End Sub
    NOW, that can be used to open either a Form or a Report. I actually opened a Form with mine but you replace the code part of Form to Report.
    ALSO, for the UnboundTextBoxes you Format for ShortDate and then use the Input Mask to then make it so all the user has to do is Type in 6 numbers as: 020104 which would pull up 02/01/04. That works wonders for me and the end users really liked it. So there you have it.....multiple ways to achieve your goal.

    hope this helps you out
    have a nice one,
    BUD

  10. #10
    Join Date
    Nov 2003
    Posts
    10
    Well, I don't know about Rusty...But I sure do appreciate all the help.

    THANKS

    Don

  11. #11
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    You're Welcome Don, if I was part of the help.

    BUD

Posting Permissions

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