Using date picker 1

[mdctsg]

Hello
I have an access database that displays the date as yyyymmdd
Example:20020406
The user must type in the date ranges to retrieve data for Crystal report I would like for them to be able to use the calendar to pick the date ranges instead. Can someone show me how to fix this?


Thanks
Bob

 

[IdoMillet]

Just make sure your date parameter has no default values.
This would cause Crystal to display a calendar when the user click the dropdown when prompted for that parameter.

Cheers,
- Ido
CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[mdctsg]

Thanks for your reply. I am still new to crystal. For the life of me I can't remember how to get to the parameter box to change it. Can you help

 

[IdoMillet]

Insert, Field Object, expand the Parameters node and select the parameter. Then click the Edit toolbar icon (pencil).

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[mdctsg]

Hello again. I am using this formula to convert from date time to date

Mid ({@ReptDate},5 , 2) + "/" + Mid ({@ReptDate},7 ,2 ) + "/" + Left ({@ReptDate},4 ) + " - " + Mid ({@ReptDate},17 ,2 ) + "/" + Right ({@ReptDate},2 ) + "/" + Mid ({@ReptDate},13 ,4 )

But when I refresh to get the calender I get that a string is missing between the in and the {?ReptDate. The cursor falls where I placed the star

Left ({BNA.PickupDate},8 ) in *{?ReptDate} and {BNA.VOID} = "N"
Am I doing something wrong

 

[synapsevampire]

Since you didn't share what's in @reptdate, it's hard to know what's going on.

What is the type of the date field stored in the access database that displays the date as yyyymmdd
Example:20020406?

I'll assume that it's a number:

Your record selection will work and likely get passed to the database (using ODBC?) if it's something like:

(
{MyTable.MyDate} >= val(totext(year(minimum({?ReptDate})),"00&quot+totext(month(minimum({?ReptDate})),"00&quot+totext(day(minimum({?ReptDate})),"00&quot)

and

{MyTable.MyDate} <= val(totext(year(maximum({?ReptDate})),&quot;00&quot+totext(month(maximum({?ReptDate})),&quot;00&quot+totext(day(maximum({?ReptDate})),&quot;00&quot)
)

and

{BNA.VOID} = &quot;N&quot;


If the Access field is a string, omit the Val function.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[mdctsg]

Sorry

The program that produces the data is access based. In that db the date is formated as year, month, date, hour, minute, seconds + yyyymmddhhmmss. The info is exported to a csv file that the report is generated off of. I had to use this because of the dollar amounts in the db sometimes doesn't reflect the true value. I cannot change the structure of the db without problems from the &quot;Company&quot;. So I use the csv.

The first formula I sent I use to convert the date to just yyyymmdd and drop the time. Will the instructions you sent work for this?

Thanks so much for your help
Bob
.

 

[dgillz]

use the NumberToDate() function to convert your 8 digit integer in a YYYYMMDD format to a real date, and then compare it to your date range parameter in the record selection formuls:

NumberToDate({YourField}) in {?DateRangeParameter}

NumberToDate() is available as a downloadable UFL on the Crystal Decisions website, or let me know and I can email it to you. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[mdctsg]

I'm still getting an error message saying a string is missing where I have the double**
Left ({BNA.PickupDate},8 ) in **{?ReptDate} and {BNA.VOID} = &quot;N&quot;

Bob

 

[mdctsg]

I did download and install the date file (Thanks)

 

[mdctsg]

If you want I can email you the db and report (336k) and perhaps you can see where I went wrong

 

[Naith]

MDCTSG,

Try this out:

Date(Left ({BNA.PickupDate},8 )) = **{?ReptDate} and {BNA.VOID} = &quot;N&quot;

Good luck with your report,

Naith

 

[dgillz]

I think Left({BNA.PickupDate},8) is a string in the 8 digit YYYYMMDD format and is not convertable to a date using the date() function. This is why I suggested the NumberToDate()
function.

Try this:

NumberToDate(ToNumber(left({BNA.PickupDate},8)))in {?ReptDate} and {BNA.VOID} = &quot;N&quot;

Notice I did not include the double asterisk. I have no idea what purpose that served, if I am missing something, please let me know.

Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[mdctsg]

Arrrrrgh!!! I think I will start over. My brain is zapped. I think first I need to get the date from yyyymmddhhmmss to mmddyyyy. Whats the best way to do this?

Sleepless in Rockville
Bob