Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Displaying two parameters (97)

    I have reports based on parameter queries which ask for two dates and then finds those records Between [First Date] and [Second Date]. How can I display these two dates on the reports?! Thanks, Andy.

  2. #2
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying two parameters (97)

    I presume you are running the report from a form where you enter the parameters; if so create two text boxes on the report and enter the parameter name as
    =[Forms]![FormName]![FirstDate] and the second one as =[Forms]![FormName]![SecondDate].

    John

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying two parameters (97)

    You can create a text box with controil source =[First Date] and another one with control source =[Second Date]. You can set the Format properties of the text boxes to the appropriate date format.

    Alternatively, you can use one text box; you must take care of the formatting in the control source:

    ="Sales between " & Format([First Date], "d-mmm-yyyy") & " and " & Format([Second Date], "d-mmm-yyyy")

    Take care to use the parameters exactly as in the query. You can use a different date format, of course.

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying two parameters (97)

    Command buttons preview the Reports, which in turn run parameter queries to obtain the two parameters.. so they cannot be referenced as control objects on the form.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying two parameters (97)

    Is your reply meant for jaf90?

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying two parameters (97)

    Oops! You're right! I've tried your text-box expression but get #Error appearing on the report. My parameter query has the criteria 'Between [Enter first date] And [Enter last date]' for a date field and I've then used the precise expressions [Enter first date] and [Enter last date] on my report?
    Can you think why I'm still getting #Error?? Thanks, Andy.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying two parameters (97)

    If you are absolutely sure that you copied the expression correctly and substituted the exact parameter names, you can try declaring the parameters in the query:
    - Open the query in design view.
    - Select Query | Parameters...
    - Type [Enter first date] and set the data type to Date/Time.
    - In the next line, type [Enter last date] ans set the data type to Date/Time.
    - Click OK.
    - Save the query.

  8. #8
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying two parameters (97)

    I've tried with and without these parameters declared as Date/Time and still get #Error. In the text box expression ="Projects Submitted Between "&[Enter first date]&" "&[Enter last date] MUST I use the Format function to format them as dates?

  9. #9
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying two parameters (97)

    Oops.. Will it make a difference if there are no records??

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying two parameters (97)

    Yes.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying two parameters (97)

    You don't need to use the Format function. If you omit Format, the dates will be displayed as they are entered by the user if you haven't declared the parameters, or in short date format if you have declared the parameters as Date/Time.

    Sometimes it helps to delete the text box and recreate it from scratch.

  12. #12
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying two parameters (97)

    Having data helped!! Thanks for all the assistance, Andy.

  13. #13
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying two parameters (97)

    How did you all know my current problem!!!

    not crucial but would be of interest:

    if there are no records - rather than getting an error report how can you cancel the report and just get a message "nothing to report" or something similar?

    Thanks

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Displaying two parameters (97)

    You can create a little msgbox macro and attach it to the On No Data event in the reports events properties tab.
    Regards,
    Rudi

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying two parameters (97)

    A report has an On No Data event. You can use this to cancel opening the report if there are no data to be displayed:

    Private Sub Report_NoData(Cancel As Integer)
    ' Cancel report
    Cancel = True
    ' Optional: show message
    MsgBox "There are no data for this report.", vbInformation
    End Sub

    If you open the report using DoCmd.OpenReport in VBA, cancelling it will cause error # 2501. You can use error handling to avoid seeing the accompanying error message. For example, in the On Click event of a command button on a form:

    Private Sub cmdReport_Click()
    On Error GoTo ErrHandler
    DoCmd.OpenReport "rptMyReport", acViewPreview
    Exit Sub

    ErrHandler:
    ' No message if error = 2501
    If Not (Err = 2501) Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

Posting Permissions

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