Displaying two parameters (97)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Is your reply meant for jaf90?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Yes.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote