Record Selection Formula Error

I'm trying to use the dynamic & cascading parameter functionality that is provided by DataLink Viewer. I am getting a "The ) is missing" error in the record selection formula. The database is Access 2000. The food report is my report. What is causing the error?

Here is the record selection formula that is in my report:

(
{?PromptFoodDate.rpt} = "ALL" OR
Cstr(Year({tblFoodPrepDates.fpdDate}),0,"") = {?PromptFoodDate.rpt}
)
AND
({Books.BookID}} in {?PromptFoodBook.rpt})


Here is the record selection formula that is in the DataLink Viewer sample report:

(DataLink_Viewer_Year_and_Product_Prompts V12B):

(
{?Prompt_Order_Year_V12.rpt} = "ALL" OR
Cstr(Year({Orders.Order Date}),0,"") = {?Prompt_Order_Year_V12.rpt}
)
AND
({Product.Product ID} in {?Prompt_Products_V12.rpt})
DbDiag.jpg
Error-1.jpg
ReportDesign.jpg
formulas.jpg
FoodMain.rpt
fields.jpg
formulas.jpg
SampleReport-DbDiag.jpg
Mark01Asked:
Who is Participating?
 
Ido MilletConnect With a Mentor Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
I had a short tech support session with the Mark and it turned out he was using a date instead of a a year as the clickable formula in the dynamic parameter report. Should be OK now.
0
 
mlmccConnect With a Mentor Commented:
Actually it is an EXTRA }

Your formula
(
{?PromptFoodDate.rpt} = "ALL" OR
Cstr(Year({tblFoodPrepDates.fpdDate}),0,"") = {?PromptFoodDate.rpt}
)
AND
({Books.BookID}} in {?PromptFoodBook.rpt})

It should be


(
{?PromptFoodDate.rpt} = "ALL" OR
Cstr(Year({tblFoodPrepDates.fpdDate}),0,"") = {?PromptFoodDate.rpt}
)
AND
({Books.BookID} in {?PromptFoodBook.rpt})

When you make that change, there is another error.  BooksID is a number and PromptFoodBook.rpt is a string.  Change the parameter to a number

mlmcc
0
 
vastoCommented:
The interface from your screenshots looks like Crystal Reports 2008 and above. If this is the case you can use the cascading parameters provided by Crystal reports.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Mark01Author Commented:
mlmcc, how do I change the parameter to a number?
0
 
Mark01Author Commented:
mlmcc, you're right. Here's the new error I'm getting.Number Range Required Error
0
 
mlmccConnect With a Mentor Commented:
In the field explorer
  Open the parameters branch
  DOuble click the parameter or right click it and choose edit

In the parameter editor
CHange the type to number

mlmcc
0
 
Mark01Author Commented:
I changed the parameter to a number but I still get the same number range required error.Parameter Dialog
0
 
mlmccCommented:
DO you want the user to enter a range for the book ids?
If so, set the property on the parameter to allow ranges.  
   Expand the parameter screen or scroll down in the Value Options pane.
   You can also allow multiple values.

If you don't want to allow a range then change the IN to = in the selection formula

mlmcc
0
 
Mark01Author Commented:
I'm working on a quick test to learn how to use the dynamic & cascading parameter functionality that is provided by DataLink Viewer. When the sample dynamic & cascading parameter functionality report runs, another report runs immediately. It shows the year first and then a list of the product types.

You can view this feature in two Millet Software videos. (1) The "View the Dynamic & Cascading Parameters (even for pre-XI reports)" video on the following page (more than halfway down the page):
http://www.milletsoftware.com/datalinkBenefit.htm

(2)
http://www.youtube.com/watch?v=SgBhstjqlU8
The part about the dynamic & cascading parameter begins at 13:50.


I'm trying to show date first and then a list of the books. I'll study the sample reports and get back to you as soon as possible.
0
 
vastoConnect With a Mentor Commented:
Mark01 , If you want to use explicitly DataLink I guess the best source for you will be to contact their customer support.
If your report version is Crystal XI and above cascading parameters are supported  by the Crystal engine. If you need to use cascading parameters with "pre XI" version check also this video : http://www.r-tag.com/Pages/Preview_CascadingParameters.aspx
It is for another viewer, which support cascading parameters without creating additional reports. Free license is available from this link: http://www.r-tag.com/Pages/FreeCrystalReportsViewer.aspx

The advantage of this model is that you can select the date from a calendar and based on the selected value to select available books. You may also have a default value for the date so you may select directly the book without changing the date.
0
 
Mark01Author Commented:
Vasto, regarding Millet Software's customer support, I never imagined they had time to provide this kind of support. Ido Millet solved the problem in a few minutes. Thank you, mlmcc, Ido and Vasto.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.