Edit Mask Numeric Field???
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17397
Printed Date: 04 May 2024 at 7:34pm
Topic: Edit Mask Numeric Field???
Posted By: siweltj
Subject: Edit Mask Numeric Field???
Date Posted: 23 Aug 2012 at 4:54am
|
Here is my dilemma - I have a SQL data set where a date field is an 8 digit numeric field as yyyymmdd. My report uses a parameter field to select the date based on user input. However users occasionally enter non-numeric characters such as commas or periods. When this occurs the SQL database freezes until the user ends program.
I was hoping to use edit mask but seems that can not work on numeric fields. Ideally this field is presented to the user as a date field however I have no way to edit the SQL database. Suggestions? Thanks |
Replies:
Posted By: DBlank
Date Posted: 23 Aug 2012 at 5:15am
|
make the param a date type to force proper selection and covert your database field in the select statement
{?date}=date(mid(totext(field,0,''),5,2)+'/'+right(totext(field,0,''),2) + '/' +left(totext(field,0,''),4))
|
Posted By: siweltj
Date Posted: 23 Aug 2012 at 5:47am
|
Thank you for the help, however gave that a try and get a "Bad Date Format" error in Record Selection. Below is my record selection formula:
{?Date} =date(mid(totext({Schedules.LDate},0,''),5,2)+'/'+right(totext({Schedules.LDate},0,''),2) + '/' +left(totext({Schedules.LDate},0,''),4)) and {EventStrings.TripCount} > 0 and {EventStrings.EvStrName} in {?From Route} to {?To Route} |
Posted By: DBlank
Date Posted: 23 Aug 2012 at 5:59am
| are you sure the Schedules.LDate is a numeric type or is it really a string that allawys has 8 numbers? Can it be blank or null? |
Posted By: siweltj
Date Posted: 23 Aug 2012 at 6:16am
Below is sample of the data from the field which is a number field. Although there is a zero in the data, this would never be a part of the selection:
|
Posted By: DBlank
Date Posted: 23 Aug 2012 at 6:19am
|
if it exists it can still choke the formula on that row
try
(
{Schedules.LDate}>0 and
{?Date} = date(mid(totext({Schedules.LDate},0,''),5,2)+'/'+right(totext({Schedules.LDate},0,''),2) + '/' +left(totext({Schedules.LDate},0,''),4))
)
and
{EventStrings.TripCount} > 0 and {EventStrings.EvStrName} in {?From Route} to {?To Route} |
Posted By: siweltj
Date Posted: 23 Aug 2012 at 7:16am
| Thank you, that works. Downside is that it has significantly increased processing time on the report and CPU usage on server side. 500 page report took nearly 10 minutes and 90%+ of peak CPU. |
Posted By: DBlank
Date Posted: 23 Aug 2012 at 7:43am
|
try and reverse it the conversion to see if it runs better
tonumber(totext({?Date},('yyyyMMdd')))={Schedules.LDate}
|
Posted By: siweltj
Date Posted: 23 Aug 2012 at 7:57am
| Tremendous improvement. Well done. Thank you. |