Changing the input date format 1

[Rich25]

Hello,

I'm using Crystal Reports on the web with an ActiveX control. My report prompts a user to enter a start date and an end date in the following form

Date(year,month,day) - This is a little confusing for non-technical users. Is there any way to allow each user to just enter something like

month/day/year?

Also, I'm sorting by location where the user has to type in a city location when prompted. Is there any way to change this from an input box where the user has to type in the location to a combo box where the locations are already there to choose from. Please help! Thanks!!!

Rich

 

[emcevoy]

Hello

Here is what you can do to fix this parameter date, when you create your parameter field, select the datatype to be string and give the user a prompting value of something that looks like this:

12-31-1999.

the exact separator character is unimportant, as long as there is one. in the menu report | edit selection formula | record..., type the following sample code:

tonumber(mid({?dateprompt},1,2)) = day ({table1.date}) and
tonumber(mid({?dateprompt},4,2)) = month ({table1.date}) and
tonumber(mid({?dateprompt},7,4)) = year ({table1.date})

in this example, the parameter is named "?dateprompt" and the database field that is being tesed is called "table1.date". in each of the above lines of code, the following happens:

1. left of the equal sign...
the mid() function searches our ?dateprompt value and parses out the characters we specify. the characters are converted from text to a number using the tonumber function and compare it to the right side of the formula.

2. right of the equal sign the day() function returns a number that represents the day for the record.
if all three lines are true, then the record is passed to the report and is formatted according to your report design.

Hope this helps.
E. McEvoy
Crystal Reports Consultant

http://www.pacificridgesolutions.com

 

[MalcolmW]

A more flexible approach (if you have a current version) is to use the string parameter, and convert it to a date value with
DateValue({?dateprompt}) = {table1.date}

DateValue will accept date strings in a variety of formats(eg "Sep 20, 2000", "January 1, 2001", "4/13/2001&quot. If the format is ambiguous, ie "03/02/01", then it chooses the standard American M/D/Y.

You can also combine it with the IsDate function to test if the date string can be converted, and provide an appropriate response if the value is not a recognized format. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[LMRollins]

How could you use this formula if the date isn't in the dbf. For example, say I wanted all dates between 1/1/2001 and 3/1/2001? but 1/1/2001 may not be a valid date.

 

[dgillz]

1/1/2001 IS a valid date. If there are no records in the db with that date, no harm, no foul.

MalcolmW's solution is very solid. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com