Solved

Record Selection Formula Error

Posted on 2014-01-06
11
756 Views
Last Modified: 2014-01-07
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
0
Comment
Question by:Mark01
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 160 total points
Comment Utility
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
 
LVL 18

Expert Comment

by:vasto
Comment Utility
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
 

Author Comment

by:Mark01
Comment Utility
mlmcc, how do I change the parameter to a number?
0
 

Author Comment

by:Mark01
Comment Utility
mlmcc, you're right. Here's the new error I'm getting.Number Range Required Error
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 160 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Mark01
Comment Utility
I changed the parameter to a number but I still get the same number range required error.Parameter Dialog
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 

Author Comment

by:Mark01
Comment Utility
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
 
LVL 18

Assisted Solution

by:vasto
vasto earned 40 total points
Comment Utility
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
 
LVL 22

Accepted Solution

by:
Ido Millet earned 300 total points
Comment Utility
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
 

Author Comment

by:Mark01
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now