Parameters added to Report

[alphaomega3]

I am getting #Name error on a report when I create an unbound text field with the following:

="Report Period From" & " " & [Startdate] & " " & "Through" & " " & [Enddate]

The query this is based on is this:

PARAMETERS Startdate DateTime, Enddate DateTime;
SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date, tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
FROM tblMaster INNER JOIN tblSupplier ON tblMaster.suppName = tblSupplier.Supplier
WHERE (((tblMaster.Date) Between [Startdate] And [Enddate]) AND ((tblMaster.Audit)=False) AND ((tblMaster.PA)=False));

What am I doing wrong?

[MMcCarthy]

I am getting #Name error on a report when I create an unbound text field with the following:

="Report Period From" & " " & [Startdate] & " " & "Through" & " " & [Enddate]

The query this is based on is this:

PARAMETERS Startdate DateTime, Enddate DateTime;
SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date, tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
FROM tblMaster INNER JOIN tblSupplier ON tblMaster.suppName = tblSupplier.Supplier
WHERE (((tblMaster.Date) Between [Startdate] And [Enddate]) AND ((tblMaster.Audit)=False) AND ((tblMaster.PA)=False));

What am I doing wrong?

Startdate and Enddate are not actually being returned by the query and therefore are not available on the Report.

Mary

[alphaomega3]

Startdate and Enddate are not actually being returned by the query and therefore are not available on the Report.

Mary

I followed the steps as outlined in the following website: http://support.microsoft.com/kb/208630 for database.

[MMcCarthy]

I followed the steps as outlined in the following website: http://support.microsoft.com/kb/208630 for database.

This will restrict the data being returned to fall between these two dates but won't make them available for use on the report. Standard way to do this would be to have two textboxes on a form to hold the two dates and a command button to open the report.

txtStartDate and txtEndDate are the two textboxes on the form. Command button (cmdOpenReport) has the following code.

Private Sub cmdOpenReport()
   DoCmd.OpenReport "ReportName", , , "[tblMaster.Date] Between " & txtStartdate & " And " & txtEnddate
End Sub
 

The record source of the report is as follows:

SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date, tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
FROM tblMaster INNER JOIN tblSupplier 
ON tblMaster.suppName = tblSupplier.Supplier
WHERE (((tblMaster.Audit)=False) AND ((tblMaster.PA)=False)));
 

Now as long as the form remains open the dates are available to you.

="Report Period From " & [Forms]![FormName]![txtStartdate] & " Through " & [Forms]![FormName]![txtEnddate]
 

Mary

[alphaomega3]

Unfortunately, I use a report call form for all reports. Using the following code:

Private Sub cmdOpenReport_Click()
 ' Purpose:  Opens the report selected in the list box.
    On Error GoTo cmdOpenReport_ClickErr
    If Not IsNull(Me.lstreports) Then
        DoCmd.OpenReport Me.lstreports, IIf(Me.ChkPreview.Value, acViewPreview, acViewNormal)
    End If
    Exit Sub
 
cmdOpenReport_ClickErr:
    Select Case Err.Number
    Case 2501   ' Cancelled by user, or by NoData event.
        MsgBox "Report cancelled, or no matching data.", vbInformation, "Information"
    Case Else
        MsgBox "Error " & Err & ": " & Error$, vbInformation, "cmdOpenReport_Click()"
    End Select
    Resume Next
End Sub

Anyway to make this work also or will this one report have to have a seperate page?

[MMcCarthy]

Unfortunately, I use a report call form for all reports. Using the following code:

Private Sub cmdOpenReport_Click()
 ' Purpose:  Opens the report selected in the list box.
    On Error GoTo cmdOpenReport_ClickErr
    If Not IsNull(Me.lstreports) Then
        DoCmd.OpenReport Me.lstreports, IIf(Me.ChkPreview.Value, acViewPreview, acViewNormal)
    End If
    Exit Sub
 
cmdOpenReport_ClickErr:
    Select Case Err.Number
    Case 2501   ' Cancelled by user, or by NoData event.
        MsgBox "Report cancelled, or no matching data.", vbInformation, "Information"
    Case Else
        MsgBox "Error " & Err & ": " & Error$, vbInformation, "cmdOpenReport_Click()"
    End Select
    Resume Next
End Sub

Anyway to make this work also or will this one report have to have a seperate page?

No just add the text boxes to this form and change the open report code to add the criteria of the dates.

Mary

[alphaomega3]

But what if the report that is being pulled up has different parameters other than dates?

[alphaomega3]

To be a bit more specific this Open Command is for all reports that are populated in a list box. Will adding the txtstartdate and txtenddate to the open command effect all reports in the list box?

[MMcCarthy]

To be a bit more specific this Open Command is for all reports that are populated in a list box. Will adding the txtstartdate and txtenddate to the open command effect all reports in the list box?

Yes it will. Unfortunately you can't have it both ways.

Mary

[alphaomega3]

Ugh....thanks for the help. I suppose I don't need it that bad. How come all the help sites say I should be able to add a text box

=Reports![reportname]![parametername]

? www.databasedev.co.uk/printing_parameters.html

They even show it in use. Is it because my date field is named "Date"?

[MMcCarthy]

Ugh....thanks for the help. I suppose I don't need it that bad. How come all the help sites say I should be able to add a text box

=Reports![reportname]![parametername]

? www.databasedev.co.uk/printing_parameters.html

They even show it in use. Is it because my date field is named "Date"?

To be honest I've never tried to use a parameter that way. What happens if you set up a control like this on your report?

BTW, ideally you should never name a field Date as it can be confused with the Date function.

Mary

[alphaomega3]

I have tried it and I get the #Name? error. I don't understand why all the websites say it works this way and it doesn't.

[MMcCarthy]

I have tried it and I get the #Name? error. I don't understand why all the websites say it works this way and it doesn't.

Let me bring this to the attention of some of the other site experts who may have heard of this.

Mary

[nico5038]

Hmm, logically it should work, but I would never use this as I dislike the two popup inputboxes.
Better to place two (hidden) fields for the startdate and enddate on your form with the listbox. (Personally I prefer to use a combo based on a "select distinct DateField from tblYours order by desc" as that will allow only to chose existing dates and thus the report will have atleast one row of data -)

Now in the listbox's onClick event I would test for the specific report like:

IF Me.lstReports = "rptDateReport" then
Me.txtStartDate.visible = true
Me.txtEndDate.visible = true
else
Me.txtStartDate.visible = false
Me.txtEndDate.visible = false
endif

Thus the fields will be visible inly for that report and you could even disable the print button to force the user to enter them. (requires additional testing in the field/combo's AfterUdate event...)
Finally use in your report for the headertext:
="Report Period From " & [Forms]![FormName]![txtStartdate] & " Through " & [Forms]![FormName]![txtEnddate]

Getting the idea ?

Nic;o)

[Rabbit]

I was able to replicate the process you provided from the microsoft sample database. Have you tried renaming your date field? Does the query run okay?

[NeoPa]

I may be missing something here, but is what you're after simply :

PARAMETERS Startdate DateTime,
           Enddate DateTime;
SELECT tblMaster.rptNumber,
       tblMaster.suppName,
       tblMaster.Date,
       tblMaster.Nonconformance,
       tblMaster.Audit,
       tblMaster.PA
       [Startdate],
       [Enddate]
FROM tblMaster INNER JOIN tblSupplier
  ON tblMaster.suppName = tblSupplier.Supplier
WHERE (((tblMaster.Date) Between [Startdate] And [Enddate])
  AND ((tblMaster.Audit)=False)
  AND ((tblMaster.PA)=False));

We're discussing some of the other issues in another thread I know so I won't comment on that here (Comparing booleans with True/False values etc).

[MMcCarthy]

I may be missing something here, but is what you're after simply :

PARAMETERS Startdate DateTime,
           Enddate DateTime;
SELECT tblMaster.rptNumber,
       tblMaster.suppName,
       tblMaster.Date,
       tblMaster.Nonconformance,
       tblMaster.Audit,
       tblMaster.PA
       [Startdate],
       [Enddate]
FROM tblMaster INNER JOIN tblSupplier
  ON tblMaster.suppName = tblSupplier.Supplier
WHERE (((tblMaster.Date) Between [Startdate] And [Enddate])
  AND ((tblMaster.Audit)=False)
  AND ((tblMaster.PA)=False));

We're discussing some of the other issues in another thread I know so I won't comment on that here (Comparing booleans with True/False values etc).

The problem is he then wants to use the parameter values in an unbound textbox on the report.

[NeoPa]

In what sense is a used TextBox unbound (ever)?
Clearly he can use the =[Startdate] form of setting the value to display. Or even =[Startdate]+7 etc. Why must it be unbound?

[MMcCarthy]

In what sense is a used TextBox unbound (ever)?
Clearly he can use the =[Startdate] form of setting the value to display. Or even =[Startdate]+7 etc. Why must it be unbound?

Look at the first code in Post#1

[NeoPa]

And why would that not work fine now - with my recently posted SQL (post #16)?

[NeoPa]

Check out post #16 for a workable solution :)

[ADezii]

I am getting #Name error on a report when I create an unbound text field with the following:

="Report Period From" & " " & [Startdate] & " " & "Through" & " " & [Enddate]

The query this is based on is this:

PARAMETERS Startdate DateTime, Enddate DateTime;
SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date,
tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
FROM tblMaster INNER JOIN tblSupplier ON tblMaster.suppName = tblSupplier.Supplier
WHERE (((tblMaster.Date) Between [Startdate] And [Enddate]) AND ((tblMaster.Audit)=False) AND ((tblMaster.PA)=False));

What am I doing wrong?

Sorry I jumped in so late on the conversation. Try adding the Parameters themselves as add ons to the SELECT Statement at the end. Do not use a Table Qualifier as in:

PARAMETERS Startdate DateTime, Enddate DateTime;
SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date,
tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA, 
 [Startdate], [Enddate]
FROM tblMaster INNER JOIN tblSupplier ON tblMaster.suppName = tblSupplier.Supplier
WHERE (((tblMaster.Date) Between [Startdate] And [Enddate]) AND ((tblMaster.Audit)=False) AND ((tblMaster.PA)=False));

[NeoPa]

That's a brilliant idea ADezii :D Check out post #16.
(Having said that of course, your post explains it better :))