By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Parameters added to Report

P: 78
I am getting #Name error on a report when I create an unbound text field with the following:
Expand|Select|Wrap|Line Numbers
  1. ="Report Period From" & " " & [Startdate] & " " & "Through" & " " & [Enddate]
The query this is based on is this:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS Startdate DateTime, Enddate DateTime;
  2. SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date, tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
  3. FROM tblMaster INNER JOIN tblSupplier ON tblMaster.suppName = tblSupplier.Supplier
  4. WHERE (((tblMaster.Date) Between [Startdate] And [Enddate]) AND ((tblMaster.Audit)=False) AND ((tblMaster.PA)=False));
What am I doing wrong?
Feb 28 '07 #1
Share this Question
Share on Google+
22 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I am getting #Name error on a report when I create an unbound text field with the following:
Expand|Select|Wrap|Line Numbers
  1. ="Report Period From" & " " & [Startdate] & " " & "Through" & " " & [Enddate]
The query this is based on is this:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS Startdate DateTime, Enddate DateTime;
  2. SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date, tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
  3. FROM tblMaster INNER JOIN tblSupplier ON tblMaster.suppName = tblSupplier.Supplier
  4. 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
Feb 28 '07 #2

P: 78
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.
Feb 28 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOpenReport()
  2.    DoCmd.OpenReport "ReportName", , , "[tblMaster.Date] Between " & txtStartdate & " And " & txtEnddate
  3. End Sub
  4.  
The record source of the report is as follows:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date, tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
  2. FROM tblMaster INNER JOIN tblSupplier 
  3. ON tblMaster.suppName = tblSupplier.Supplier
  4. WHERE (((tblMaster.Audit)=False) AND ((tblMaster.PA)=False)));
  5.  
Now as long as the form remains open the dates are available to you.
Expand|Select|Wrap|Line Numbers
  1. ="Report Period From " & [Forms]![FormName]![txtStartdate] & " Through " & [Forms]![FormName]![txtEnddate]
  2.  
Mary
Feb 28 '07 #4

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOpenReport_Click()
  2.  ' Purpose:  Opens the report selected in the list box.
  3.     On Error GoTo cmdOpenReport_ClickErr
  4.     If Not IsNull(Me.lstreports) Then
  5.         DoCmd.OpenReport Me.lstreports, IIf(Me.ChkPreview.Value, acViewPreview, acViewNormal)
  6.     End If
  7.     Exit Sub
  8.  
  9. cmdOpenReport_ClickErr:
  10.     Select Case Err.Number
  11.     Case 2501   ' Cancelled by user, or by NoData event.
  12.         MsgBox "Report cancelled, or no matching data.", vbInformation, "Information"
  13.     Case Else
  14.         MsgBox "Error " & Err & ": " & Error$, vbInformation, "cmdOpenReport_Click()"
  15.     End Select
  16.     Resume Next
  17. End Sub
Anyway to make this work also or will this one report have to have a seperate page?
Feb 28 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Unfortunately, I use a report call form for all reports. Using the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOpenReport_Click()
  2.  ' Purpose:  Opens the report selected in the list box.
  3.     On Error GoTo cmdOpenReport_ClickErr
  4.     If Not IsNull(Me.lstreports) Then
  5.         DoCmd.OpenReport Me.lstreports, IIf(Me.ChkPreview.Value, acViewPreview, acViewNormal)
  6.     End If
  7.     Exit Sub
  8.  
  9. cmdOpenReport_ClickErr:
  10.     Select Case Err.Number
  11.     Case 2501   ' Cancelled by user, or by NoData event.
  12.         MsgBox "Report cancelled, or no matching data.", vbInformation, "Information"
  13.     Case Else
  14.         MsgBox "Error " & Err & ": " & Error$, vbInformation, "cmdOpenReport_Click()"
  15.     End Select
  16.     Resume Next
  17. 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
Feb 28 '07 #6

P: 78
But what if the report that is being pulled up has different parameters other than dates?
Feb 28 '07 #7

P: 78
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?
Feb 28 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 1 '07 #9

P: 78
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
Expand|Select|Wrap|Line Numbers
  1. =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"?
Mar 1 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Expand|Select|Wrap|Line Numbers
  1. =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
Mar 1 '07 #11

P: 78
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.
Mar 1 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 1 '07 #13

nico5038
Expert 2.5K+
P: 3,072
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)
Mar 1 '07 #14

Rabbit
Expert Mod 10K+
P: 12,315
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?
Mar 1 '07 #15

NeoPa
Expert Mod 15k+
P: 31,186
I may be missing something here, but is what you're after simply :
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS Startdate DateTime,
  2.            Enddate DateTime;
  3. SELECT tblMaster.rptNumber,
  4.        tblMaster.suppName,
  5.        tblMaster.Date,
  6.        tblMaster.Nonconformance,
  7.        tblMaster.Audit,
  8.        tblMaster.PA
  9.        [Startdate],
  10.        [Enddate]
  11. FROM tblMaster INNER JOIN tblSupplier
  12.   ON tblMaster.suppName = tblSupplier.Supplier
  13. WHERE (((tblMaster.Date) Between [Startdate] And [Enddate])
  14.   AND ((tblMaster.Audit)=False)
  15.   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).
Mar 1 '07 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
I may be missing something here, but is what you're after simply :
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS Startdate DateTime,
  2.            Enddate DateTime;
  3. SELECT tblMaster.rptNumber,
  4.        tblMaster.suppName,
  5.        tblMaster.Date,
  6.        tblMaster.Nonconformance,
  7.        tblMaster.Audit,
  8.        tblMaster.PA
  9.        [Startdate],
  10.        [Enddate]
  11. FROM tblMaster INNER JOIN tblSupplier
  12.   ON tblMaster.suppName = tblSupplier.Supplier
  13. WHERE (((tblMaster.Date) Between [Startdate] And [Enddate])
  14.   AND ((tblMaster.Audit)=False)
  15.   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.
Mar 1 '07 #17

NeoPa
Expert Mod 15k+
P: 31,186
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?
Mar 1 '07 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 1 '07 #19

NeoPa
Expert Mod 15k+
P: 31,186
And why would that not work fine now - with my recently posted SQL (post #16)?
Mar 1 '07 #20

NeoPa
Expert Mod 15k+
P: 31,186
Check out post #16 for a workable solution :)
Mar 2 '07 #21

ADezii
Expert 5K+
P: 8,597
I am getting #Name error on a report when I create an unbound text field with the following:
Expand|Select|Wrap|Line Numbers
  1. ="Report Period From" & " " & [Startdate] & " " & "Through" & " " & [Enddate]
The query this is based on is this:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS Startdate DateTime, Enddate DateTime;
  2. SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date,
  3. tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
  4. FROM tblMaster INNER JOIN tblSupplier ON tblMaster.suppName = tblSupplier.Supplier
  5. 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:
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS Startdate DateTime, Enddate DateTime;
  2. SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date,
  3. tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA, 
  4. [Startdate], [Enddate]
  5. FROM tblMaster INNER JOIN tblSupplier ON tblMaster.suppName = tblSupplier.Supplier
  6. WHERE (((tblMaster.Date) Between [Startdate] And [Enddate]) AND ((tblMaster.Audit)=False) AND ((tblMaster.PA)=False));
Mar 2 '07 #22

NeoPa
Expert Mod 15k+
P: 31,186
That's a brilliant idea ADezii :D Check out post #16.
(Having said that of course, your post explains it better :))
Mar 2 '07 #23

Post your reply

Sign in to post your reply or Sign up for a free account.